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
- macros created in excel on vba mac cannot be executed normally on windows
- excel vba scraping cannot get the class under the class without id or name tag
- excel vba cannot automate screen clicks
- [excel vba] variable declared in for cannot be used as a function argument
- vba excel save with the same book as overwrite save
- excel vba i want to draw continuous shapes
- single quotation when updating data in excel vba database
- vba - cannot store the total value extracted by the auto filter in a variable
- excel vba sort collapses
- excel vba [i want to auto filter by the value of the list box]
- excel vba double quotation is added when outputting to a text file
- vba - i'm having trouble with excel macros i want to copy the contents of a sheet and paste it as it is on a new sheet
- vba "run-time error '1004'" error that cannot be copied
- i want to get the column number with the excel vba large function
- [super beginner] how to make excel vba class
- vba - powershell cannot be executed
- excel vba cell time value to list box
- i want to search and paste it with excel vba-find
- vba - [excel] i want to calculate the lowest price in 3 days on another sheet
- vba - how to launch an access file with a password from task scheduler
- vba - [database design] about table design when referencing/updating the access database using excel as an input screen
- vba - please teach me how to centrally manage customer lists
- vba - how to search in an access form like an excel autofilter
- access&excelvba how to handle blank data
- vba - search and extract a specific string with a function in google sheets
- about vba user form combo box
- vba - i want to insert a space only when it starts from the far right in the report
- vba - access tabular form change the text color for each record depending on yesno of the check box of each record