santa12345
Board Regular
- Joined
- Dec 2, 2020
- Messages
- 66
- Office Version
- 365
- Platform
- Windows
Hello.
I have the following script that is working.
A ID table is opened, txtDist= ID command pulls one ID at a time.
Query output is named output and then transfered to a output tab in a output.xls file
Renames the file and then loops again until it finishes the entire ID table.
I want to keep this but add around ID output .. lets say ID2 which would then output to output2 and transfers to the same excel file but a separate tab (output2) and then renames the file like below and loops.
Any ideas would be greatly appreciated.
Thanks.
----
Private Sub Command1_Click()
DoCmd.SetWarnings 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("IDs", dbOpenTable)
rs.MoveFirst
Do Until rs.EOF
txtDist = rs![ID]
'Set objFolder = objFSO.CreateFolder("C:\test\" & txtDist)
DoCmd.OpenQuery "Distributor Output", , acReadOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "output", "C:\output.xls", True
RetVal = Shell("cmd /c copy /y C:\output.xls C:\test\" & txtDist & ".xls", vbHide)
rs.MoveNext
Sleep (50000)
Loop
DoCmd.SetWarnings -1
End Sub
I have the following script that is working.
A ID table is opened, txtDist= ID command pulls one ID at a time.
Query output is named output and then transfered to a output tab in a output.xls file
Renames the file and then loops again until it finishes the entire ID table.
I want to keep this but add around ID output .. lets say ID2 which would then output to output2 and transfers to the same excel file but a separate tab (output2) and then renames the file like below and loops.
Any ideas would be greatly appreciated.
Thanks.
----
Private Sub Command1_Click()
DoCmd.SetWarnings 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("IDs", dbOpenTable)
rs.MoveFirst
Do Until rs.EOF
txtDist = rs![ID]
'Set objFolder = objFSO.CreateFolder("C:\test\" & txtDist)
DoCmd.OpenQuery "Distributor Output", , acReadOnly
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "output", "C:\output.xls", True
RetVal = Shell("cmd /c copy /y C:\output.xls C:\test\" & txtDist & ".xls", vbHide)
rs.MoveNext
Sleep (50000)
Loop
DoCmd.SetWarnings -1
End Sub