Export error

S

Sin

The following program is used to export my query results from db to xls. In
every export the results will automatically overwrites the existing result in
the respective xls sheet named identical to the query, however, the outcome
seems to be very unstable, i.e. I will get error 3190 every every now and
then, can anyone let me know if I can stablise my code?
Note: I have formulas in the xls, therefore, I need the result to overwrites
the existing result for the formulas to work.

Private Sub CreateReport_Click()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "SR_AllG",
"S:\P & F\Reports\SR_Reports.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "SR_AllC",
"S:\P & F\Reports\SR_Reports.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "SR_SelectG",
"S:\P & F\Reports\SR_Reports.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "SR_SelectC",
"S:\P & F\Reports\SR_Reports.xls", True

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Open existing workbook
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open("S:\P & F\Reports\SR_Reports.xls",
ReadOnly:=True)

'Add data to cells of a worksheet
Set oSheet = oBook.Worksheets("Misc")
oSheet.Range("c2").Value = Forms!frReportFilter.BK
oSheet.Range("c3").Value = Forms!frReportFilter.Office
oSheet.Range("c4").Value = Forms!frReportFilter.Product
oSheet.Range("c5").Value = Forms!frReportFilter.Class
oSheet.Range("c5").Value = Forms!frReportFilter.UR

oSheet.Range("c8").Value = Forms!frReportFilter.frMonth
oSheet.Range("c9").Value = Forms!frReportFilter.toMonth

'Open Excel
oExcel.Visible = True

'Release object reference
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top