andyb7901
asked on
Access VBA - Import Spreadsheet
Hi,
Can someone tell me how to simply insert a spreadsheet into an access table using VBA. I am using the code attached but it wants a specific column avilable for each row in the spreadsheet. My spreadsheet simply has one row ( at the moment ) with a series of numbers in it. But when I import to my access table it gives me an error cant find column F1 in table. Why F1? If I change my column to F1 it works. Is there anyway to insert the data in column 1 of the spreadsheet into column 1 of my table and so on.
Dim strTargetTable As String
strTargetTable = "tbl_test"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTargetTable, Me.txtFilePath.Value, False
Thanks
Can someone tell me how to simply insert a spreadsheet into an access table using VBA. I am using the code attached but it wants a specific column avilable for each row in the spreadsheet. My spreadsheet simply has one row ( at the moment ) with a series of numbers in it. But when I import to my access table it gives me an error cant find column F1 in table. Why F1? If I change my column to F1 it works. Is there anyway to insert the data in column 1 of the spreadsheet into column 1 of my table and so on.
Dim strTargetTable As String
strTargetTable = "tbl_test"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTargetTable, Me.txtFilePath.Value, False
Thanks
Rey Obrero (Capricorn1)
to insert the data from the excel file to your table, the column names in the excel file must have a matching field in your target table.
Access needs to allocate column names to the input - you should provide them in the first row of your sheet to match the ones in strTargetTable. If you don't, Access will allocate its own - F1 for the first column, F2 for the second, etc.
F1 is short for Field 1.
F1 is short for Field 1.
ASKER
I have named a column in excel. Grid A1 is named spn_id and so is my database table field
How you approach this depends if this is a one off or a once in a while process or if you need the import to be regular.
When importing a spreadsheet you need to make sure of the following:
1) That the column names are valid field names. For example:
MyField is ok but My.Field, My'SillyField and My"StrageField will cause errors and there are may other examples of bad field names.
2) When the operator is working on the sheet, it is very easy to click to the side of the sheet and For example in column J, but you only have headings for columns A to I.
So before you import the data you need to Click on the column to the right of the last column Say J for example, the hold the left button down and move the mouse to the right so that you select all columns from J to IV, then hit the delete key.
You can also open the sheet and import the data using software this is the most reliable way of ensuring that it works every time. Also you can control data validation which could be a huge advantage.
When importing a spreadsheet you need to make sure of the following:
1) That the column names are valid field names. For example:
MyField is ok but My.Field, My'SillyField and My"StrageField will cause errors and there are may other examples of bad field names.
2) When the operator is working on the sheet, it is very easy to click to the side of the sheet and For example in column J, but you only have headings for columns A to I.
So before you import the data you need to Click on the column to the right of the last column Say J for example, the hold the left button down and move the mouse to the right so that you select all columns from J to IV, then hit the delete key.
You can also open the sheet and import the data using software this is the most reliable way of ensuring that it works every time. Also you can control data validation which could be a huge advantage.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.