Below is the code I am using. I am trying to create a workbook and export queries to the workbook but I am getting an error stating several possible reasons:
1. Path doesn't exsist
2. File is being used by another program
3. Work book I am trying to use has the same name as an open workbook.
I don't currently have a workbook with that name, I am letting the macro below create one and then close it and send the queries to it. I am using this code in another database and it works like a charm. When I step into the code it highlights the following section of code:
1. Path doesn't exsist
2. File is being used by another program
3. Work book I am trying to use has the same name as an open workbook.
I don't currently have a workbook with that name, I am letting the macro below create one and then close it and send the queries to it. I am using this code in another database and it works like a charm. When I step into the code it highlights the following section of code:
Code:
xlbook.SaveAs ("C:\Documents and Settings\Desktop\ATB Review" & GrpNum & "ATBReview.xls")
Code:
Function ATB_Analysis()
Dim GrpNum As String
Dim xlapp As Object
Dim xlbook As Object
' Module Create By Brian Conner 6/2/2009
GrpNum = Form_FrmGroupNumber.Text0.Value
Set xlapp = CreateObject("Excel.Application")
Set xlbook = xlapp.workbooks.Add
xlbook.SaveAs ("C:\Documents and Settings\Desktop\ATB Review" & GrpNum & "ATBReview.xls")
xlbook.Close
'Export ATB Review Reports to Excel Workbook on Desktop
DoCmd.TransferSpreadsheet acExport, , "Rej_Detail", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
DoCmd.TransferSpreadsheet acExport, , "Unresponded_Detail", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
DoCmd.TransferSpreadsheet acExport, , "CPC Non Contracted", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
DoCmd.TransferSpreadsheet acExport, , "*4 Appeals", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
DoCmd.TransferSpreadsheet acExport, , "*5 616 FSC", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
DoCmd.TransferSpreadsheet acExport, , "*5 FSC 616 Summary", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
DoCmd.TransferSpreadsheet acExport, , "*6 B7 Cerdentialing", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
DoCmd.TransferSpreadsheet acExport, , "*6 B7 Cerdentialing Summary", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
DoCmd.TransferSpreadsheet acExport, , "*9 Credit Report", "c:\Documents and Settings\Desktop\" & GrpNum & "ATBReview.xls"
End Function
