Removing first lines of Excel worksheet

[John J.]

I need to automate the import of an Excel worksheet. The worksheet's first 7
lines are used for heading with joined cells. Is there a way in Access to
automatically remove those 7 lines before importing the worksheet?
Thank you.
John

 

[Brian]

I'm sure the MVP's will have a better answer, but here is my poor man's
answer (i.e. what I have done in a similar circumstance).

1. Write (or record) a macro named Auto_Open in an otherwise empty Excel
workbook that opens, the sheet in question, removes the top 7 lines, saves it
as the file to be imported into Access, then closes it and the workbook with
the macro in it. The Auto_Open name will make it run when the control
workbook is opened. You can create the macro by recording it, and then modify
the macro as needed for the live/automated environment.
2. Use the Shell command in Access to open the workbook with the macro. Put
TransferText or TransferSpreadsheet following the Shell command.

E.G.

Dim OpenExcel As Variant
Dim ControlFile as String
Dim ImportFile as String
ControlFile = "C:\NameOfControlFile.xls"
ImportFile = "C:\NameOfFileToBeImported.xls"

OpenExcel = Shell("Excel.exe " & """" & ControlFile & """", 0)
'ensure file has been created before continuing
Do While Len(Dir(ImportFile )) = 0 'will be zero until the Shell above has
created it
Loop
DoCmd.TransferSpreadsheet...

 

[John J.]

Thanks Brian.
I'll check it out.

I'm sure the MVP's will have a better answer, but here is my poor man's
answer (i.e. what I have done in a similar circumstance).

1. Write (or record) a macro named Auto_Open in an otherwise empty Excel
workbook that opens, the sheet in question, removes the top 7 lines, saves
it
as the file to be imported into Access, then closes it and the workbook
with
the macro in it. The Auto_Open name will make it run when the control
workbook is opened. You can create the macro by recording it, and then
modify
the macro as needed for the live/automated environment.
2. Use the Shell command in Access to open the workbook with the macro.
Put
TransferText or TransferSpreadsheet following the Shell command.

E.G.

Dim OpenExcel As Variant
Dim ControlFile as String
Dim ImportFile as String
ControlFile = "C:\NameOfControlFile.xls"
ImportFile = "C:\NameOfFileToBeImported.xls"

OpenExcel = Shell("Excel.exe " & """" & ControlFile & """", 0)
'ensure file has been created before continuing
Do While Len(Dir(ImportFile )) = 0 'will be zero until the Shell above has
created it
Loop
DoCmd.TransferSpreadsheet...