Hello all,
I've been looking at the post here for Access to Excel export of data and I have a found a few however what I am looking for exactly is a code or some type of function
that can export the selected Data from a query without creating another worksheet just replacing the already available data let's say from A2:A100.
This is the function i am currently using:
Code:
Sub exportspreadsheet()
On Error GoTo HandleError
Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Excel.Workbook
Dim db As DAO.Database
Set db = CurrentDb
conPath = GetPath(db.Name)
'delete the spreadsheet
Kill conPath & "BYWEEKLYREPORT.xlsx"
' create a workbook from the template
Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(conPath & "BYWEEKLYREPORT.xltx")
'objXLApp.Visible = True
objXLBook.SaveAs (conPath & "BYWEEKLYREPORT.xlsx")
objXLBook.Close
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryDeptWeeklyReport", conPath & "BYWEEKLYREPORT.xlsx", True
Dialog.Box "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf & "where the application sits for ""BYWEEKLYREPORT.xlsx"""
ProcDone:
On Error Resume Next
' Let's clean up our act
Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set objResultsSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing
ExitHere:
Exit Sub
HandleError:
Select Case err.Number
Case 3265
Resume Next
Case 1004
Set objXLBook = objXLApp.Workbooks.Open(conPath & "BYWEEKLYREPORT.xlsx")
Resume Next
Case 53
Resume Next
Case 75
Resume Next
Case Else
Dialog.Box err.Description, vbExclamation, _
"Error " & err.Number
End Select
Resume ProcDone
End Sub
however that function does not export data in the sheet I want it to, it creates another sheet that is not formatted.
to get a better idea I have attached the Excel file so anyone can see the light red shaded area, that's where i want the data to go every time IBYWEEKLYREPORT4.zip export.