Checking for a blank line in excel from Access

[irethedo]

I have a spreadsheet that I am doing a TransferSpreadsheet function from within Access

    DoCmd.TransferSpreadsheet acImport, 8, "NewData_tbl", In_File, True

but sometimes the top line of the spreadsheet is a blank line which causes a Run-time error '2391' Field 'NoName' doesn't exist in destination table 'New Data_tbl'.

Is there a way within Access to check the spreadsheet for a blank line first and then remove it before performing the TransferSpreadsheet function?

Thanks

 

[Andrzejek]

Yes.
[ul]
[li]Create an Excel object[/li]
[li]Open your Excel file[/li]
[li]if first row is empty - remove row[/li]
[li]Save file[/li]
[li]DoCmd.TransferSpreadsheet acImport, 8, "NewData_tbl", In_File, True[/li]
[/ul]


---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

Create an Excel object
Open your Excel file
if first row is empty - remove row
Save file
DoCmd.TransferSpreadsheet acImport, 8, "NewData_tbl", In_File, True

Thanks Andy

Can this be totally done with VBA from access without openning the spreadsheeet?

 

[SkipVought]

In order to perform ANY operations in Excel, including deleting rows, Excel must be opened.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

.... unless you are talking about CSV file like this person here? I ask because many people confuse XLS or XLSX with CSV (simple text file), -- as long as they can double-click on a file in Windows Explorer and open it in Excel, it is Excel file to them.

And if it IS and Excel file, why don't you want to do it "without openning the spreadsheeet"?

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

However, a .csv file is not a file that you can do a TransferSpreadsheet on.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]