Delete first row and Column for excel import

[kazman101]

Hi Guy's

I need to import some data into Access from an Excel sheet, the data was extracted from Business Objects which seems to have an annoying limitation where it adds a blank column and a Blank row into the spreadsheet before adding the data

is there a way to remove a Blank Column and Row before importing the data into Access

as it is now my column headings are on the second row and start in the second column

can anyone help?

[rpeare]

You can try this code from MS Access:

Sub main()

Dim objExcel As Object
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet

Set objExcel = CreateObject("excel.application")
On Error GoTo ERRHANDLER

Debug.Print "CP1"
Set objWorkbook = objExcel.Workbooks.Open("c:\test_file_3.xls")
Debug.Print "CP2"
Set objSheet = objWorkbook.Worksheets("FirstSheet")

Debug.Print "CP3"
objSheet.Columns("A:A").Select
Debug.Print "CP4"
Selection.Delete 'shift:=xlToLeft
Debug.Print "CP5"
objSheet.Rows("1:1").Select
Debug.Print "CP6"
Selection.Delete 'shift:=xlUp
Debug.Print "CP7"

objWorkbook.Save
objExcel.Application.Quit
Set objExcel = Nothing
Set objWorkbook = Nothing
Set objSheet = Nothing

Exit Sub

ERRHANDLER:
    Debug.Print Err.Number & " " & Err.Description
    objExcel.Application.Quit
    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objSheet = Nothing
    
End Sub

I haven't figured it out but for some reason it works sometimes and doesn't others and I think it's because it's not correctly closing the file or something but it does work when it runs without this error:

91 Object variable or With block variable not set

on this line

Selection.Delete 'shift:=xlToLeft

but at least you have a starting point

I suspect it will work for you if you don't open the file at all you just try to process it.