I need to export table to excel template and save as different name

[niloufar]

I have looked everywhere and since I don't know alot about VBA I don't really know after exporting into my file how TO save my template to different name.




Please help me.


here is my Code:

With myRecordset
          Do Until .EOF
          
          'join report table with my request table's Rows
          'Create newworksheet for each report joint with requestTable rows
                Set qdf = dbsReport.CreateQueryDef("Template", _
                " do some join")
                
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TempFile", xlsxTempPath, True, SHEETNAME




                Here I would Like to Save this workbook to different name, Plzzz...




                DoCmd.DeleteObject acQuery, "Template"
                
          
       
          Loop
       End With

[ItsMe]

Here is a code snippet.
https://www.accessforums.net/program...tml#post229060

[niloufar]

I have looked everywhere and since I don't know alot about VBA I don't really know after exporting into my file how TO save my template to different names in each loop and after creating first file it will errors for the second loop."Run-time error'91': object var or with block var not set" What is that? how can be fixed?


Please help me.


here is my Code:

     With myRecordset
              Do Until .EOF
              
              'join report table with my request table's Rows
              'Create newworksheet for each report joint with requestTable rows
                    Set qdf = dbsReport.CreateQueryDef("Temp", _
                    " do some join")
                        
    
    xlsxSheet = rs.Fields("[Name]")
                    
                    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Temp", xlsxPath, True, "FlatFile"
                    
                    Set objXL = New Excel.Application
                    Set objWkb = objXL.Workbooks.Open(xlsxPath)
                    
                    objXL.Visible = False
                    ActiveWorkbook.SaveAs Filename:=SaveAsPath & xlsxSheet & ".xlsx", FileFormat:=51, CreateBackup:=False
                    objWkb.Close
                    Set objWkb = Nothing
                    objXL.Quit
                    Set objXL = Nothing
                                   
    
                    DoCmd.DeleteObject acQuery, "Temp"
                    
           
              Loop
           End With

[June7]

If you want to loop recordset, need .MoveNext in the loop. Otherwise the loop is reading the same record over and over and would be an endless loop. However, something is triggering error so you are not yet experiencing the endless loop. Maybe saving the file to the same file name again is causing the break. What line triggers the error?