DataBase - How can I import an unstandard Excel worksheet into Access 2007?
Asked By M Skabialka
03-Feb-10 01:39 PM
I would like to bring an Excel spreadsheet into Access where I will analyze
it line by line and create multiple tables from it. Worksheet rows have
many different kinds of information, which I can extract logically in
Access.
I can copy and paste from Excel to Access, but when I try to use code I get
errors
strTable = "tblStatus"
strPathFile = "C:\import.xls"
WorkSheetName = "Dallas"
blnHasFieldNames = False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames, Range:=WorkSheetName &
2391 Field 'F1' does not exist in destination table 'tblStatus.'
I just want to dump the whole worksheet into Access, matching columns and
rows without regard to original field names, then do my analysis.
In Excel I imported it into another worksheet to remove all formatting,
merged cells, etc, and that is what I want to import into Access.
How can I do that without getting errors?
DoCmd.TransferSpreadsheet
(1)
Access 2007
(1)
Excel
(1)
VBA
(1)
WorkSheetName
(1)
TransferSpreadsheet
(1)
TblStatus
(1)
AcImport
(1)
KARL DEWEY replied to M Skabialka
Why not just link it?
--
Build a little, test a little.
M Skabialka replied to KARL DEWEY
I ended up writing an import specification to copy it form Excel into a new
Access table, then copied that line by line into my import table, then used
the code I had already written to break out the data.
The user clicks a button and browses to the Current file and selects it, and
then VBA code pulls it from that spreadsheet to another with a standard
sheet name, uses the import spec to pull that sheet into Access, and
completes the data transformation into tables without further user
intervention.
It would have been easier though to find some way to use TransferSpreadsheet
to ignore field names!
Mich
KARL DEWEY replied to M Skabialka
If you link the Excel and then append you can align any field name to any so
long as datatype is a match.
--
Build a little, test a little.
Export tbl von Access 2007 nach Excel 2007: Umlaufe gehen verloren DataBase Hallo liebe Helfer im Hintergrund, ich habe Access 2007 Sp1, Excel 2007 SP1, Wnows SP 3 und bin ganz stoz darauf, dass ich mit DoCmd.OutputTo acOutputTable verschwunden; dafür stehen Hieroglyphen. In VBA habe ich alle Verweise auf die Version 12.0 (Access, Office, Excel). Woran liegt das, wie kann ichs ändern. Vielen Dank für Eure Hilfe. Wilhelm Hanselmann Access Discussions DoCmd.TransferSpreadsheet (1) DoCmd.OutputTo (1) Access 2007 (1) Excel 2007 (1) Office (1
TransferSpreadsheet Excel 2007 into Access 2003 DataBase How can I TransferSpreadsheet Excel 2007 into Access 2003? This does not work: DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblSomeData", "C: \ DataFolder \ SomeData.xlsx", True, "Sheet1$" If I save as an xls it will work: DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "tblSomeData", "C: \ DataFolder \ SomeData.xls", True, "Sheet1$" Do I need to do that? Is
Cannot export Access 2007 reports to Excel 2007 DataBase I would really like to know whose bright idea it was to get rid of this feature in Access 2007. In Access 2002 and Access 2003, I created so many reports (Crosstab in particular with many many calculated fields that was an important feature or not BEFORE removing it. . .Does anyone else have this problem?! Access Reports Discussions DoCmd.TransferSpreadsheet (1) Office 2003 (1) Access 2003 (1) Access 2007 (1) Accounting
TransferSpreadsheet to .xlsx format DataBase I am trying to use DoCmd.TransferSpreadsheet to export some Access 2007 data to an Excell 2007 spreadsheet. I use the .xlsx file extension with my filename, but after the code is run I try opening the newly created file from Excel 2007 and it says it does not recognize the file format. Any idea on what I may be doing wrong? TIA - Ken strOutputFile = strPath & " \ Output \ UserList.xlsx" DoCmd.TransferSpreadsheet TransferType: = acExport, TableName: = "tblID", filename: = strOutputFile, Hasfieldnames: = True Access Modules DAO Discussions DoCmd.TransferSpreadsheet (1
Convert Access 2007 to 2003 errors DataBase I developed an Access database in access 2007 with multiple functions extracting from and exporting into Excel worksheets. Then I found out the user was incorrect in thinking they had Office 2007, they have Office 2003, and had downloaded a converter for Word and Excel 2007 files. I converted the database to Access 2003 and it runs OK in Access 2007, but on a test machine with only