500 Excel Spreadsheets

  • Thread starter Thread starter NotGood@All
  • Start date Start date
N

NotGood@All

I have about 500 excel spreadsheets that I need to import into Access. I did
the first 150 by hand and all of them are the same so I would like to create
a macro to do the rest but I don’t know what to do! I do the following by
hand. File/get external data/import – double click the file – click next –
click 'first row contains column headings' – click OK – click finish – click
yes to overwite existing table -- I get a window saying finished importing
file – click OK -- Then I go to 3 queries that I run to import the temp
table, update the name columns, and the 3rd query I have to modify before I
run it. It updates a field that says what spreadsheet the information came
from. Can I create a macro to complete these steps??
 
Get you started? That is what the example code at the link I provided is
intended to do.

I have no knowledge of your database setup, your EXCEL workbooks /
worksheets and their structure, etc. So there's no way I can provide
specific suggestions at this time.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"open a adobe file from a command button"
 
Ken, thanks, I missed that!
--
NotGood@All


Ken Snell MVP said:
Get you started? That is what the example code at the link I provided is
intended to do.

I have no knowledge of your database setup, your EXCEL workbooks /
worksheets and their structure, etc. So there's no way I can provide
specific suggestions at this time.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"open a adobe file from a command button"
Click to expand...
 
Ken, thanks. I used the “Import Data from ALL Excel Files in a single folder
via transferspreadsheet†and it works great. One last question on this
subject. Can you tell me how to import the file name along with the data. I
need to show whick spreadsheet the information comes from.

Thanks again
 
You can't import the filename with the original data in the
TransferSpreadsheet action via VBA TransferSpreadsheet. You could do it if
you have a column in EXCEL that ocntains the filename, then the filename
would be one of the imported columns. Otherwise, you'd need to run an update
query after the TransferSpreadsheet action, where the update query writes
the filename into a existing field in the table into which you imported the
spreadsheet's data.
 
Back
Top