1

Running VBA through Access

I have 7 select queries in an access database that I will be doing TransferSpreadsheet with that all go on the same Excel sheet.

Currently I am just attempting to set a variable with the next empty cell with a start point of A23.

In my attempt to locate the next empty cell I continue to get run-time error 1004.

I've inserted PATH for fpath variable for confidentiality.

Error is occurring when I try to set the n1 variable. If I comment out setting the n1 variable then the error occurs on the MsgBox....

Private Sub Command0_Click()

Dim n1 As String
Dim n2 As String
Dim n3 As String
Dim n4 As String
Dim n5 As String
Dim n6 As String
Dim n7 As String
Dim start As String
Dim fpath As String
Dim strFileExists
Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim xldata As Excel.Range

'Set start cell on the CAECAN file
start = "A23"

fpath = "PATH"
strFileExists = Dir(fpath)
If strFileExists <> "" Then
    Set xlapp = CreateObject("Excel.Application")
    Set xlbook = xlapp.Workbooks.Open(fpath)
    Set xlsheet = xlbook.Worksheets("Audit Remittance CAD invoices")
    With xlapp
        .Visible = False
        .DisplayAlerts = False
        .Workbooks.Open fpath
        With xlsheet
            n1 = Range(start).End(xlDown).Offset(1).Select
            MsgBox "The first empty cell in column A is: " & Range(start).End(xlDown).Offset(1).Address
            End With
        End With
    Else
        MsgBox "File does not exist"
End If
Dennis
  • 21
  • 5
  • Definitely shouldn't be a `.Select` there btw. – BigBen Sep 21 '22 at 13:37
  • 1
    `n1 = .Cells(.Rows.Count, "A").End(xlUp).Value` if you want the value in the last cell. Or `.Address` if you want its address. – BigBen Sep 21 '22 at 13:38
  • 1
    slightly altered the code to give me the cell address for the next row – Dennis Sep 21 '22 at 13:48
  • n1 = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Address – Dennis Sep 21 '22 at 13:48
  • To not stay empty-handed, feel free to download [my working file](https://drive.google.com/file/d/1ii2-6jQCwNu65CaEKhjiXVghx_G_6Syj/view?usp=sharing) from Google Drive, to see how you could tackle this. It has just a form and a module with the code. Btw, whenever you have the need to number variables, you want to use a data structure, probably an *array* in this case (related to `n1...n7`). Also, `Dim xlapp As Excel.Application` doesn't work if you haven't created a reference to the Excel library hence you would want to use the early bound `Set xlapp = New Excel.Application`. – VBasic2008 Sep 21 '22 at 15:59

0 Answers0