importing many excel files

G

Guest

I am working with an access database and about 800 excel files. What I need
to do is import these xls files into an access database. I really don't know
where to start with the macros, modules or vba's. The xls files all have the
same header info and there is only one sheet per xls file. I found this info
below on another post, but I am not sure how to get it to work, or if it will
even work in my situation.


Dim strFile As String
Dim strFolder As String

strFolder = "H:\Reporting\OrdersWaitingFraud\XL\"
strFile = Dir$(strFolder & "*.xls")
Do While Len(strFile) > 0

' put your code here to use the TransferText method
' The full path to the file will be strFolder & strFile

strFile = Dir$()
Loop


Thanks
 
G

Guest

That is exactly what you need. It does assume, however, that you will be
importing every .xls file in the folder. It does need a couple of things,
but I need to know whether you will be importing all the files to the same
table or a table per sheet, and if you are deleting the existing data in the
table or adding to it.
 
G

Guest

Yes, I will be importing all the files to the same table and I want to add to
the data in the table. In addition, is it possible to check for duplicates
before importing? I do not want to import duplicates based on column A if
possible, but I guess I could get them out later if this cannot be done.
Thanks for the all of the help.
 
G

Guest

Do you mean duplicate values in column A of a spreadsheet or a duplicate of
column A in the Access table, or both?
 
G

Guest

There are several excel files that have duplicate rows. Column A is the
order_number column in the xls files. So I do not want to duplicate rows or
order_numbers when importing the xls files to the access table if I can help
it, but I can always clean it up after its imported.
 
G

Guest

Any ideas with this yet? I am stumped.

Klatuu said:
Do you mean duplicate values in column A of a spreadsheet or a duplicate of
column A in the Access table, or both?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top