Exporting with VBA problem

[Xarkath]

Hello,


I am using the following code to export 2 queries into a single Excel sheet. The code is working as intended until I rename the database. I had requested some help with testing and gave the database a new name so that it would not replace the master copy. At that point the code starting throwing errors stating that it could not find the Excel workbook. As soon as I changed the database name back it started working again.

Can anyone explain to me why a name change (to the db not the Excel document) would affect this or how to get around it? Also, what part of the following code would even be dependent on recognizing the database name?

Thanks


Private Sub AccAdj_Click()
Dim rstName As Recordset
Dim objApp As Object, objMyWorkbook As Object, objMySheet As Object, objMyRange As Object
DoCmd.SetWarnings False
DoCmd.OpenQuery "MedAdj_List_qry"
DoCmd.OpenQuery "DenAdj_List_qry"
DoCmd.OpenQuery "VisAdj_List_qry"
DoCmd.OpenQuery "OtherAdj_List_qry"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "AcACt_qry", "Accrual_AdjustmentList.xlsx", True, "Accrual_Adjustments"
Set rstName = CurrentDb.OpenRecordset("AcSB_qry")

Set objApp = CreateObject("Excel.Application")
Set objMyWorkbook = objApp.Workbooks.Open("Accrual_AdjustmentList.xlsx ")
Set objMySheet = objMyWorkbook.Worksheets("Accrual_Adjustments")
Set objMyRange = objMySheet.Cells(objApp.ActiveSheet.UsedRange.Rows .Count + 2, 1)
With objMyRange
rstName.MoveFirst 'Rewind to the first record
.Clear
.CopyFromRecordset rstName
End With
objMyWorkbook.Save
objApp.Quit

Set ojbMyWorkbook = Nothing
Set objApp = Nothing
End Sub

[ranman256]

Changing the db name should have NO effect on the export code. 1 does not effect the other.
tho looking at yr code, there is no reason to start an excel object....the transferspreadsheet command is all you need to export data.
The record set is not needed.

[Xarkath]

Thanks for the reply. I didn't think the name change should have had an affect either but that is all that changed fromt the time it was working to the time the errors were thrown.

As for the additional code, I was under the impression that it was specific to exporting the data from the second query to the same worksheet and that with out it the second export would be applied to a new worksheet. Is that not true? I am still learning Access, SQL and VBA so I tend to take stuff at face value when someone is trying to help me out.

Thanks

[ranman256]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, vQry, vFile, True, vSheet

The last param is the sheet name. So 3 things:
1. it will export to the same sheet everytime and the existing data gets written over BUT if the # records is smaller than the existing data there, it WONT erase the extra records.
2. to prevent the extra old records, you can delete the existing workbook before the export
3. if you cant erase the existing workbook, you can use the excel object to delete the existing workSHEET, save, THEN export.