Hi,
I work for a parent company that has several affiliate companies associated with it. Each month we have to run 'Affiliate' asset reports, the data for which is stored in an Access table (of course). I found the following VBA code from this site, and it works perfectly:
Dim sFileName As String, sPath As String, sDateStampM As String, sDateStampY As String, sSpace1 As String, sSpace2 As String
Dim DateCalc As Date
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objsht As Excel.Worksheet
Dim rst As Recordset
Dim iRow As Integer
'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\Monthly_Affiliate_Template.xls")
'--- I always name my worksheets something specific, and this next
'--- line refers to a specific worksheet (Excel names the first
'--- one "sheet 1" by default)
Set objsht = objWkb.Worksheets("Sheet1")
'--- Note: I use a preset workbook so that headers and footers are already
'--- configured to the project's standards, and in some cases,
'--- the headers for columns are already configured, along with
'--- column widths, etc. You could just as easily '--- use a template that contains a plain, default worksheet.
'--- since this particular worksheet had various built in headers
'--- and column titles, the actual data starts at row 10
DateCalc = DateAdd("m", -1, Date)
sPath = "C:\" 'change path here
sFileName = "Affiliate_Report_NSP"
sSpace1 = " "
'sDateStamp = Format(Now(), "mmm")
sDateStampM = Format(DateCalc, "mmm")
sSpace2 = "-"
sDateStampY = Format(Now(), "yyyy")
iRow = 10
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qry_Affiliate_Report_NSP " & _
"WHERE ((Affiliate_ID)= 'NSP') " & _
"order by Emera_Name, Asset_Tag_ID;")
rst.MoveFirst
objsht.Range("RPTTITLE").Select
objsht.Range("RPTTITLE") = "MONTHLY AFFILIATE REPORT - NSP"
objsht.Range("AFFEXPORT").Select
Do While Not rst.EOF
objsht.Cells(iRow, 1).Value = rst!Asset_Item_No
objsht.Cells(iRow, 2).Value = rst!Asset_Tag_ID
objsht.Cells(iRow, 3).Value = rst!Affiliate_ID
objsht.Cells(iRow, 4).Value = rst!Location_ID
objsht.Cells(iRow, 5).Value = rst!UserIDofRecord
objsht.Cells(iRow, 6).Value = rst!Item_Type
objsht.Cells(iRow, 7).Value = rst!Manufact_ID
objsht.Cells(iRow, 8).Value = rst!Man_Model_Name
objsht.Cells(iRow, 9).Value = rst!Emera_Name
objsht.Cells(iRow, 10).Value = rst!Centennial_Last_Reported
iRow = iRow + 1
rst.MoveNext
Loop
Pause (120)
'File =sPath & sFileName Might fit in nice to do this way so you have a varialble to call the file
ActiveWorkbook.SaveAs (sPath & sFileName & sSpace1 & sDateStampM & sSpace2 & sDateStampY)
Pause (5)
ActiveWorkbook.Close
Set objsht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
rst.Close
End Function
This exports the data (based on a query) to an external Excel template file, and then renames (Save As) the file with a Date Stamp. The problem is that I have the same code setup for all six affiliates, but when I try to run them in succession (6 separate queries being executed via a Macro), it hangs with an error message.
Run-time Error '91' .... Object Variable or With Block not set ..... When I go to 'Debug' , it does not seem to like this line:
ActiveWorkbook.SaveAs (sPath & sFileName & sSpace1 & sDateStampM & sSpace2 & sDateStampY).
Does anyone know why this is happening ? I want to have all of these reports created by one click of a button ?
Thanks !
I work for a parent company that has several affiliate companies associated with it. Each month we have to run 'Affiliate' asset reports, the data for which is stored in an Access table (of course). I found the following VBA code from this site, and it works perfectly:
Dim sFileName As String, sPath As String, sDateStampM As String, sDateStampY As String, sSpace1 As String, sSpace2 As String
Dim DateCalc As Date
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objsht As Excel.Worksheet
Dim rst As Recordset
Dim iRow As Integer
'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\Monthly_Affiliate_Template.xls")
'--- I always name my worksheets something specific, and this next
'--- line refers to a specific worksheet (Excel names the first
'--- one "sheet 1" by default)
Set objsht = objWkb.Worksheets("Sheet1")
'--- Note: I use a preset workbook so that headers and footers are already
'--- configured to the project's standards, and in some cases,
'--- the headers for columns are already configured, along with
'--- column widths, etc. You could just as easily '--- use a template that contains a plain, default worksheet.
'--- since this particular worksheet had various built in headers
'--- and column titles, the actual data starts at row 10
DateCalc = DateAdd("m", -1, Date)
sPath = "C:\" 'change path here
sFileName = "Affiliate_Report_NSP"
sSpace1 = " "
'sDateStamp = Format(Now(), "mmm")
sDateStampM = Format(DateCalc, "mmm")
sSpace2 = "-"
sDateStampY = Format(Now(), "yyyy")
iRow = 10
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qry_Affiliate_Report_NSP " & _
"WHERE ((Affiliate_ID)= 'NSP') " & _
"order by Emera_Name, Asset_Tag_ID;")
rst.MoveFirst
objsht.Range("RPTTITLE").Select
objsht.Range("RPTTITLE") = "MONTHLY AFFILIATE REPORT - NSP"
objsht.Range("AFFEXPORT").Select
Do While Not rst.EOF
objsht.Cells(iRow, 1).Value = rst!Asset_Item_No
objsht.Cells(iRow, 2).Value = rst!Asset_Tag_ID
objsht.Cells(iRow, 3).Value = rst!Affiliate_ID
objsht.Cells(iRow, 4).Value = rst!Location_ID
objsht.Cells(iRow, 5).Value = rst!UserIDofRecord
objsht.Cells(iRow, 6).Value = rst!Item_Type
objsht.Cells(iRow, 7).Value = rst!Manufact_ID
objsht.Cells(iRow, 8).Value = rst!Man_Model_Name
objsht.Cells(iRow, 9).Value = rst!Emera_Name
objsht.Cells(iRow, 10).Value = rst!Centennial_Last_Reported
iRow = iRow + 1
rst.MoveNext
Loop
Pause (120)
'File =sPath & sFileName Might fit in nice to do this way so you have a varialble to call the file
ActiveWorkbook.SaveAs (sPath & sFileName & sSpace1 & sDateStampM & sSpace2 & sDateStampY)
Pause (5)
ActiveWorkbook.Close
Set objsht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
rst.Close
End Function
This exports the data (based on a query) to an external Excel template file, and then renames (Save As) the file with a Date Stamp. The problem is that I have the same code setup for all six affiliates, but when I try to run them in succession (6 separate queries being executed via a Macro), it hangs with an error message.
Run-time Error '91' .... Object Variable or With Block not set ..... When I go to 'Debug' , it does not seem to like this line:
ActiveWorkbook.SaveAs (sPath & sFileName & sSpace1 & sDateStampM & sSpace2 & sDateStampY).
Does anyone know why this is happening ? I want to have all of these reports created by one click of a button ?
Thanks !
