Idarac
asked on
Access import Excel worksheets
I have 5 workbooks that were given to me as examples for export. I have to write a Access VBA routine that will import these excel spreadsheets into Access. These are only example files there will be others that have to be imported on a weekly basis so I can't just do a one time import. There are 2 date columns in each worksheet Start Date and End Date.
If I use the files as they are and try and import using the TransferSpreadsheet method the Start Date and End date are not imported into Access. The columns (fields) are null.
But if I open then save each excel file then the start date and end date does import. I am using Excel and Access 2007.
The start date and end date cells are formatted as =DATE(2013,12,7)
My code
strWorksheet = "Key Measures"
strTable = "tblSobeysPOS"
For x = 1 To Filecnt - 1
strFile = FileNameArray(x)
strFile = theFileDirectory & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, _
strFile, blnHasFieldNames, _
strWorksheet & "$"
Next x
If I use the files as they are and try and import using the TransferSpreadsheet method the Start Date and End date are not imported into Access. The columns (fields) are null.
But if I open then save each excel file then the start date and end date does import. I am using Excel and Access 2007.
The start date and end date cells are formatted as =DATE(2013,12,7)
My code
strWorksheet = "Key Measures"
strTable = "tblSobeysPOS"
For x = 1 To Filecnt - 1
strFile = FileNameArray(x)
strFile = theFileDirectory & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, _
strFile, blnHasFieldNames, _
strWorksheet & "$"
Next x
aikimark
It looks like you are going to overwrite your import table with every iteration in your For...Next loop
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This would seem a good place to use a macro.
With your version of Access, you can import the spreadsheet.
This will start a wizard that will let you choose the correct data type and format for your date columns. When finished, choose Save Import.
Then, you can create a macro to run that saved import wizard the next time you want the tables. Of course, you can stat this macro with a button on a form.
If you really want VBA code, when this is working, you can convert the macro to VBA code within Access.
BTW, I am confused on your date issue. =date(2014,06,06) inserts a date in a cell, but does not format it. Did you mean to say the format was "yyyymmdd"?
With your version of Access, you can import the spreadsheet.
This will start a wizard that will let you choose the correct data type and format for your date columns. When finished, choose Save Import.
Then, you can create a macro to run that saved import wizard the next time you want the tables. Of course, you can stat this macro with a button on a form.
If you really want VBA code, when this is working, you can convert the macro to VBA code within Access.
BTW, I am confused on your date issue. =date(2014,06,06) inserts a date in a cell, but does not format it. Did you mean to say the format was "yyyymmdd"?
@Richard Daneke
While the import specification wizard is much better in A2007 than A2003 and even more improved in A2010, it's still not bulletproof. Access DOES NOT parse every row of a spreadsheet and pick the right datatype. And even when you specify it in the wizard, Access occasionally overrides your wishes. And then there are the many questions that get posted here about problems using or tweaking Saved Imports from VBA.
I wouldn't go there if I didn't have to -- and I don't!
While the import specification wizard is much better in A2007 than A2003 and even more improved in A2010, it's still not bulletproof. Access DOES NOT parse every row of a spreadsheet and pick the right datatype. And even when you specify it in the wizard, Access occasionally overrides your wishes. And then there are the many questions that get posted here about problems using or tweaking Saved Imports from VBA.
I wouldn't go there if I didn't have to -- and I don't!
ASKER
"The start date and end date cells are formatted as =DATE(2013,12,7)"
What I meant to say was.
In excel the cell date formula =DATE(2013,8,4)
Foramtted as DATE - (03/14/2001)
What I meant to say was.
In excel the cell date formula =DATE(2013,8,4)
Foramtted as DATE - (03/14/2001)
ASKER
Here is how I set up my text imports where InventoryImport is the spec I have defined.
DoCmd.TransferText acImportDelim, "InventoryImport", TableName:="tblnventory", _
FileName:=strFile, HasFieldNames:=True
Is it possible to do the same in TransferSpreadsheet method? Set up a spec? Or some other spreadsheet import method?
DoCmd.TransferText acImportDelim, "InventoryImport", TableName:="tblnventory", _
FileName:=strFile, HasFieldNames:=True
Is it possible to do the same in TransferSpreadsheet method? Set up a spec? Or some other spreadsheet import method?
If you check the VBA help, you will see that the DoCmd.TransferSpreadsheet syntax is almost the same:
ASKER
Ruchard I do not see an import spec as in the import text.
You still haven't said if the sheet's structure changes, and where you are importing to -- new or existing tables.
I think you may be making this much harder on yourself than it needs to be
Download the sample and unzip it c:\import
Open 'The Sheet.xls' and look at the columns
Now in Excel, create a new wookbook.
Keep the same 5 columns.
Put in a whole whack of rows of data.
Overcopy 'The Sheet.xls' with your new spreadsheet.
Close Excel
Open import.mdb
Voila! Your data is there
Run qryAppendImport to add it to a local table.
Done.
Anytime you have new data to import that has the same five fields, overcopy 'The Sheet.xls' and its a done deal.
You have 5 workbooks.
Copy them.
Remove all but one row of data.
Ensure that the one row has data in it that each column will become the correct field data type in Access.
Save the files
Name the files
Link the files.
Build any append queries you need to move the data around.
Done.
Weekly then, overcopy the files with the new ones and run the queries.
Or automate the whole nine yards of the overcopying and query running.
Push up the five sample files with one row of data in each of them and I can flange it up in a sample for you.
Nick67
Import.zip
I think you may be making this much harder on yourself than it needs to be
Download the sample and unzip it c:\import
Open 'The Sheet.xls' and look at the columns
Now in Excel, create a new wookbook.
Keep the same 5 columns.
Put in a whole whack of rows of data.
Overcopy 'The Sheet.xls' with your new spreadsheet.
Close Excel
Open import.mdb
Voila! Your data is there
Run qryAppendImport to add it to a local table.
Done.
Anytime you have new data to import that has the same five fields, overcopy 'The Sheet.xls' and its a done deal.
You have 5 workbooks.
Copy them.
Remove all but one row of data.
Ensure that the one row has data in it that each column will become the correct field data type in Access.
Save the files
Name the files
Link the files.
Build any append queries you need to move the data around.
Done.
Weekly then, overcopy the files with the new ones and run the queries.
Or automate the whole nine yards of the overcopying and query running.
Push up the five sample files with one row of data in each of them and I can flange it up in a sample for you.
Nick67
Import.zip
The design for the tablename in the TransferSpreadsheet command sets your import spec. Your date fields woud be defined as date fields in the table definition.
Remember to delete records in that table if you want only to have the transferred files.
This earlier post may help on your TransferSpreadsheet:
https://www.experts-exchange.com/Database/MS_Access/A_10731-MS-Access-Overcoming-Data-Type-Issues-When-Using-DoCmd-TransferSpreadsheet.html
I know Nick67 harshly criticized my initial suggestion, but if your date columns contain only dates, you won't need to reparse each line on import.
Remember to delete records in that table if you want only to have the transferred files.
This earlier post may help on your TransferSpreadsheet:
https://www.experts-exchange.com/Database/MS_Access/A_10731-MS-Access-Overcoming-Data-Type-Issues-When-Using-DoCmd-TransferSpreadsheet.html
I know Nick67 harshly criticized my initial suggestion, but if your date columns contain only dates, you won't need to reparse each line on import.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both gave great answers