UtterAccess Discussion Forums > Import directory of Excel files into Access DB

Full Version: Import directory of Excel files into Access DB

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

Kokice

Jun 5 2007, 08:50 AM

I have a directory with thousands of invoices in Excel format. We are moving to a new AR system and I need to extract the addresses of all customers (one per invoice) out of the Excel files into an Access table. The good news is that for the most part, the addresses in each invoice are at A9:A13. I am familiar with VBA in Access and have found examples here on how to import the range A9:A13 into Access, but only for a single, explicitly defined Excel file. Is there any way to automate this in Access VBA to let it "chew" on the entire directory of Excel files, extracting A9:A13 from each and plunking the data into a table with fields NAME, ADD1, ADD2, CITY, STATE, etc.?

Thanks in advance!!

Doug Steele

Jun 5 2007, 12:15 PM

To read all of the Excel files in a given directory, you can use code like:

CODE

Dim strFile As String
Dim strFolder As String

  strFolder = "C:\Some Folder\Some Other Folder\"
  strFile = Dir(strFolder & "*.xls")
  Do While Len(strFile) > 0

[color="green"]' At this point, strFolder & strFile is the complete path to a workbook. [/color]
[color="green"]' Run the code you have that works for a single workbook.[/color]

    strFile = Dir()
  Loop

Kokice

Jun 5 2007, 04:05 PM

Thanks! That worked great. The data is coming into the Access table fine, but because the address is A10:A13 (therefore, vertical), it is imported into Access as different records (vertical) as opposed to going across to different fields. Any idea on how to remedy this?

Thanks again

Kokice

Jun 5 2007, 04:06 PM

oh, sorry....i'm using DoCmd.TransferSpreadsheet to get the data

Doug Steele

Jun 5 2007, 08:42 PM

I thought you said you had code to do the import you wanted, "but only for a single, explicitly defined Excel file".

Personally, I'd use Automation. I'd open the spreadsheet, read the 4 values in those positions, then close the spreadsheet.

CODE

Dim xlApp As Object
Dim wkbCurr As Object
Dim wksCurr As Object
Dim strA10 As String
Dim strA11 As String
Dim strA12 As String
Dim strA13 As String
Dim strFolder As String
Dim strFile As String
Dim strSQL As String

  Set xlApp = CreateObject("Excel.Application")
  strFolder = "C:\Some Folder\Some Other Folder\"
  strFile = Dir(strFolder & "*.xls")
  Do While Len(strFile) > 0
    Set wbkCurr = xlApp.Workbooks.Open(strFolder & strFile)
    Set wksCurr = wbkCurr.Sheets(1)
    With wksCurr
      strA10 = .Cells(1, 10)
      strA11 = .Cells(1, 11)
      strA12 = .Cells(1, 12)
      strA13 = .Cells(1, 13)
    End With
    wbkCurr.Close SaveChanges:=False
    strSQL = "INSERT INTO MyTable(Field1, Field2, Field3, Field4) " & _
      "VALUES ('" & strA10 & "', '" & strA11 & "', '" & strA12 & "' , '" & strA13 & "')"
    CurrentDb.Execute strSQL, dbFailOnError
    strFile = Dir()
  Loop
  xlApp.Application.Close
  Set xlApp = Nothing

Note that I've assumed that all 4 fields are text. If they're numeric, you'd leave out the single quotes.

Kokice

Jun 6 2007, 09:21 AM

sorry for the confusion. I thought I had it working and was only half right. Your last post helped tremendously. Problem solved! Thank you

jfrank

Jul 2 2007, 04:03 PM

This mostly worked for me as well, however my SQL INSERT INTO command has so many "VALUES" that it exceeds the 1st line.

I tried a couple of different continuation characters: "_" and "& _" and neither works.

How can I continue the VALUES part of the INSERT INTO command over more than 1 line?

Thank you.

Doug Steele

Jul 2 2007, 06:14 PM

The continuation character is _, and must be preceded by a space.

If you're trying to continue a text string, you have to terminate the string (with a quote), and use an ampersand to continue with the string on the next line.

strExample = "This is the text that will be on the first line, " & _
"while this will be the text on the second line."

WildBird

Jul 2 2007, 06:35 PM

The other way to deal with test strings is to use it like this

strExample = "This is the text that will be on the first line, "
strExample = strExample & "while this will be the text on the second line."

jfrank

Jul 2 2007, 07:43 PM

Thank you both.

That worked.

jfrank

Jul 4 2007, 10:35 AM

One more item:

I am now getting the following:

Run Time Error '3075'

Syntax error (missing operator) in query expression "(very long text in the Supp_Prof field I am importing from my 2nd Excel file)

- This is the last field I am importing from Excel into the table entry in the INSERT INTO command.
- The first Excel worksheet imported without incident.
- The last field was defined as text in the table, but was truncating so I switched it to memo, which allowed all of the information (more than 255 characters) in the 1st workbook's last field to be imported.

The following are the
- The cell assignment code is: Supp_Prof = .Cells(48, 2)
- The INSERT INTO code is: Supp_Prof)" & _ [the VALUES information follows]
- The VALUES code is: " & Supp_Prof & "')" [end of the strSQL assignment]

- There does not seem to be any unusual characters in the imported text

Any ideas on what is occurring and how to fix it?

Thank you.

jfrank

Jul 6 2007, 03:19 PM

I found my problem. The field I am importing has an apostrophe ( ' ) in it. Is there a way to either strip it out before INSERTing or allow acceptance of that type (and other types) of punctuation?

Thank you.

jfrank

Aug 15 2007, 02:03 PM

Another question.

The Excel files I am importing into Access have a message box popped up when opened.

Is there a way within my vba code to turn off the message box either universally or for each file after the Excel open command (Set xlApp = CreateObject("Excel.Application"); Set wbkCurr = xlApp.Workbooks.Open(strFolder & strFile)?

Thank you.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.