Home>

Thanks for your help.
This is an Excel VBA beginner.
The following code can be found in Access VBA.
From access to Excel T daily report table to existing Excel file as a new sheet
Export, delete the first line, and then
I'm trying to paste this into the line just below the last line of the existing sticking sheet.
However, in the process of getting the last line, 1004 application definition or object definition error appears.
If i remove the END property, it will proceed properly, but you cannot rely on the END property to get the last row.
Also, the copy process gets stuck in xlPasteValues ​​after .PasteSpecial.
Please teach me what to do.

Public Function Startexec ()
Dim rs As Recordset
Const xlMinimized = -4140
Dim stpathname As String
Dim stTbl As String
Dim stvardir As String
Dim stFilename As String
Dim stSheet As String
Dim stSheetQuery As String
Dim Excel As Object
Dim wb As Object
Dim llastrow As Long

    stSheet = "for pasting"
    stpathname = "\ Here is the path of the file"

    stFilename = "practice .xlsm"

    stvardir = stpathname&"\"&stFilename'Specify an Excel file.

    stTbl = "T Daily Report"'Specify an Access file.
    DoCmd.TransferSpreadsheet acExport, _
            acSpreadsheetTypeExcel9, stTbl, stvardir, False
    Set rs = CurrentDb.OpenRecordset (stTbl)

    rs.MoveLast
    stSheetQuery = "T Daily Report"
    Set Excel = CreateObject ("Excel.Application")'Excel
        Excel.WindowState = xlMinimized'Minimize
        Excel.Visible = True'Display
    Set wb = Excel.Workbooks.Open (stvardir)'Open excel file
        wb.Sheets (stSheetQuery) .Rows (1) .Delete'Delete the first row of the query sheet
        wb.Application.DisplayAlerts = False'Hide message
        llastrow = wb.Sheets (stSheetQuery) .Cells (wb.Sheets (stSheetQuery) .Rows.Count, 1) .End (xlUp) .Row
  'Here, 1004 application definition or object definition error appears.

        wb.Sheets (stSheetQuery) .Range ("A1: W"&rs.RecordCount) .Copy'Copy
        wb.Sheets (stSheet) .Range ("A"&llastrow) .Offset (1, 0) .PasteSpecial xlPasteValues

        wb.Application.DisplayAlerts = True'Redisplay message
        Excel.DisplayAlerts = False'No warning
        wb.Sheets (stSheetQuery) .Delete'Delete query sheet
        Excel.DisplayAlerts = True'Allow warning
        wb.Close True'Close
    Excel.Quit'Exit Excel

    Exit Function
  • Answer # 1

    If it is not set as a reference in Excel, the Excel constant called xlUp cannot be used, so you need to specify it numerically.

    'llastrow = wb.Sheets (stSheetQuery) .Cells (wb.Sheets (stSheetQuery) .Rows.Count, 1) .End (xlUp) .Row
         llastrow = wb.Sheets (stSheetQuery) .Cells (wb.Sheets (stSheetQuery) .Rows.Count, 1) .End (-4162) .Row


    You can find the numbers by looking at the help.

    xldirection enumeration (Excel) | Microsoft Docs


    If you set the reference, not only can you use Excel constants, but you can also use the selection list (intellisense) when entering the code.

    Start Excel (Automation): Access VBA | Immediate effect technique | Moug to learn Excel VBA