Updating Excel spreadsheet from within the Access (1 Viewer)

[reglarh]

I have VBA code to create an Access database from three Excel spreadsheets. This has worked successfully for a good while. One of the Spreadsheets, obtained from an external source, contains a header row above the column headings and two comments rows at the end. The user sometimes forgets to remove these rows before running the VBA code to create the database. As a result I have tried to incorporate this with the Access code. Without success!

I can delete the first row with a problem, but cannot get the single line to find the last used row to work. I use:
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
I just get 'sub or function' error. Searching for help produces a number of example that use:
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

but neither work.

If i remove this, the firts

The attached code is the relevant section - ignore the 'exit sub', it's there for testing.

If fDialog.Show = True Then
filename = fDialog.SelectedItems(1)
MsgBox filename
'Sub UpdateExcelFromAccess()
    ' Set a reference to the Excel Application
    Dim xlApp As Object
    Dim LastRow As Long
    Set xlApp = CreateObject("Excel.Application")
    
    ' Specify the Excel workbook and worksheet
    Dim xlWorkbook As Object
    Dim xlWorksheet As Object
    'Set xlWorkbook = xlApp.Workbooks.Open("C:\Path\To\Your\Workbook.xlsx") ' Replace with the actual path
        Set xlWorkbook = xlApp.Workbooks.Open(filename) ' Replace with the actual path

    Set xlWorksheet = xlWorkbook.Sheets("Sheet1") ' Replace with the actual sheet name
    xlWorksheet.Activate
   ' Find the last row
    With ActiveSheet
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    End With
    
   ' Delete the last two rows
    xlWorksheet.Rows(LastRow - 1 & ":" & LastRow).Delete
    
   ' Delete first row
     xlWorksheet.Rows(1).EntireRow.Delete
    ' Save and close the Excel workbook
      MsgBox xlWorkbook.Sheets("Sheet1").Cells(1, 1)
    xlWorkbook.Save
    xlWorkbook.Close
    
    ' Quit Excel
    xlApp.Quit
    
    ' Release the objects
    Set xlWorksheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing

Exit Sub


DoCmd.TransferSpreadsheet acImport, 9, "Groups", filename, True
End If

 

[adhoustonj]

No promises, but try:

LastRow = Cells(Rows.Count, 1).End(-4162).Row

I had do something similar but it was for finding the last column. I had to use .End(-4159).Column
Apparently with late binding the xlUP doesn't mean the same versus if you were using early binding and had Microsoft Excel Object Library references added.

 

[reglarh]

No go! The word Cells is highlighted with the message 'Sub or function not defined'

I have not tried to embed an Excel routine into Access before, but, as I said, just deleting the first row works and the modified spreadsheet is correctly saved. So I presume that the interfaces that allow access to the Excel spreadsheet are correct.

 

[pbaldy]

I use:

lngLastRow = xl.Cells.SpecialCells(11).Row

 

[adhoustonj]

I missed this.

    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Or

    LastRow = xlWorksheet.Cells(Rows.Count, 1).End(xlUp).Row

 

[ebs17]

    Dim xlApp As Object
    Dim LastRow As Long
    Set xlApp = CreateObject("Excel.Application")

As you can see, you are using late binding. Based on this code design, it doesn't matter that you may have set a reference.
With late binding, the constants, here the constants of the Excel object, are unknown to the calling program. Hence the imprecise demand.

You could use the real values for the constants, but it is better to declare the constants explicitly in the code.

Const xlUp = -4162

    Set xlWorksheet = xlWorkbook.Sheets("Sheet1") ' Replace with the actual sheet name
    ' xlWorksheet.Activate    <= nonsense
    ' Find the last row
    With xlWorksheet    ' if you have object variables, you also use them for clarity in actions
       ' when accessing from outside, the reference must be very clean
       LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

 

[Gasman]

No promises, but try:

LastRow = Cells(Rows.Count, 1).End(-4162).Row

I had do something similar but it was for finding the last column. I had to use .End(-4159).Column
Apparently with late binding the xlUP doesn't mean the same versus if you were using early binding and had Microsoft Excel Object Library references added.

It does not recognise enumerators if you use late binding. How can it?

XlDirection enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

VBA Range.End (xlDown, xlUp, xlToRight, xlToLeft) - Automate Excel

In this ArticleRange End Property SyntaxMoving to the Last CellCounting RowsCreating a Range Array This tutorial will show you how to use the Range.End property in VBA. Most things that you do manually in an Excel workbook or worksheet can be automated in VBA code. If you have a range of...

www.automateexcel.com

 

[reglarh]

That works!

I must read up on late binding.

Thank you.

 

[CJ_London]

If importing (rather than exporting) you can just use sql to import, specifying the top left of the range to exclude the top row and use a criteria to exclude the bottom two rows