Export same table in the same workbook in diff worksheet

[a7n9]

Hi,

I'm running some simulations in Access using VBA and I create a table named "Results".

I've created "Export button" to export the Results table to the file selected by the user. However, I'm not able to export this table with a different name or change the worksheet name.

Here is the code I'm using, can someone please modify this so that, the results table will be exported to the worbook I select but with a different worksheet name every time.

Here's the code I got till now<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdExport_Click()
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> errorhandler1
<SPAN style="color:#00007F">Dim</SPAN> strOPFilename <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> obj1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
<SPAN style="color:#00007F">If</SPAN> fIsAppRunning("Excel") <SPAN style="color:#00007F">Then</SPAN>
  )  )  ) <SPAN style="color:#00007F">Set</SPAN> obj1 = GetObject(, "Excel.Application")
<SPAN style="color:#00007F">Else</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> obj1 = CreateObject("Excel.Application")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
strOPFilename = obj1.GetSaveAsFilename(, "Excel files (*.xls), *.xls", , "Select the file name/location")
<SPAN style="color:#00007F">If</SPAN> strOPFilename = "" <SPAN style="color:#00007F">Or</SPAN> strOPFilename = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    MsgBox "Not a valid filename, try again", vbCritical, Prog_TItle
    <SPAN style="color:#00007F">GoTo</SPAN> errorhandler1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Results", strOPFilename

<SPAN style="color:#00007F">Set</SPAN> obj1 = <SPAN style="color:#00007F">Nothing</SPAN>
MsgBox "Results exported to " & strOPFilename & " succesfully!", vbInformation
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN> <SPAN style="color:#007F00">'if there is no error exit the sub</SPAN>
errorhandler1:
<SPAN style="color:#007F00">'If Err.Number = 2501 Then</SPAN>
<SPAN style="color:#007F00">'    MsgBox "Output location/file was not selected, Results were not exported", vbCritical, Prog_TItle</SPAN>
<SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> msg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    msg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description
    MsgBox msg, vbCritical, Prog_TItle & "-Error", Err.HelpFile, Err.HelpContext
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Set</SPAN> obj1 = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>>

 

[a7n9]

No. It does not have a worksheet named "Results", but my workbook's name is Results, should that matter?