Transfer Spreadsheet Code

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Look at the code below. I ran into a problem. I do not want the first row of
the excel file to be translated as the header when transfered into the
database which is what happens with the code below. The current excel file
has values on row 1 on the first sheet that differs from the 2nd and 3rd
spreadsheet thus generating an error that does not allow the other tabs to be
appended to the desired table in access.

Worksheet # 1:
Column A Column B Column C
012497 John Doe Bridgeport
435856 Jane Doe Norwalk

Worksheet # 2

Column A Column B Column C
012345 Mike Doe Stamford
987654 Will Doe Stratford

So what happens is that when the 1st sheet is loaded, the headers become
012497 etc... Then when attempting to load Worksheet # 2, since the value
(012345) differs from the header from worksheet #1, this will not append to
the source table.


'----- start of example code -----
Sub ImportExcel()

On Error GoTo Err_Handler

Const conErrInvalidSheet = 3125
Dim strImportFile As String
Dim strTargetTable As String

strImportFile = "C:\Your Folder\YourFile.xls"
strTargetTable = "tblYourTable"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet1$"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet2$"

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
strTargetTable, strImportFile, True, "Sheet2$"

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = conErrInvalidSheet Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub
'----- end of example code -----
 
The 5th value in your TransferSpreadsheet command tells Access to use the
fiirst row as fieldnames
Change "True" to "False" and it won't.
 
The 5th value in your TransferSpreadsheet command tells Access to use the
fiirst row as fieldnames
Change "True" to "False" and it won't.
 
KC,

You Rock!! It Worked!! Thank You!!

KC-Mass said:
The 5th value in your TransferSpreadsheet command tells Access to use the
fiirst row as fieldnames
Change "True" to "False" and it won't.
Click to expand...
 
KC,

You Rock!! It Worked!! Thank You!!

KC-Mass said:
The 5th value in your TransferSpreadsheet command tells Access to use the
fiirst row as fieldnames
Change "True" to "False" and it won't.
Click to expand...
 
Back
Top