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
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