How to "Open Files"

[Tamrak]

Good morning,

I have a little difficulty regarding how to write the command to open the files. My Codes are as follow:

Sub 1_Report()

Dim strP1 As String
Dim strP2 As String
Dim strP3 As String
Dim strP4 As String
Dim strP5 As String

DoCmd.SetWarnings (WarningsOff)

strP1 = "C:\Mytest\Test1" & ".xls"
strP2 = "C:\Mytest\Test2" & ".xls"
strP3 = "C:\Mytest\Test3" & ".xls"
strP4 = "C:\Mytest\Test4" & ".xls"
strP5 = "C:\Mytest\Test5" & ".xls"

If Dir(strP1) <> "" Then Kill (strP1)
If Dir(strP2) <> "" Then Kill (strP2)
If Dir(strP3) <> "" Then Kill (strP3)
If Dir(strP4) <> "" Then Kill (strP4)
If Dir(strP5) <> "" Then Kill (strP5)

DoCmd.OutputTo acOutputQuery, "QryTest1", acFormatXLS, strP1
DoCmd.OutputTo acOutputQuery, "QryTest2", acFormatXLS, strP2
DoCmd.OutputTo acOutputQuery, "QryTest3", acFormatXLS, strP3
DoCmd.OutputTo acOutputQuery, "QryTest4", acFormatXLS, strP4
DoCmd.OutputTo acOutputQuery, "QryTest5", acFormatXLS, strP5

DoCmd.SetWarnings (WarningsOn)

MsgBox "Reports Completed", vbOKOnly

'DoCmd.Quit

End Sub
****************************************************************

My objective is to have five (5) Excel files opened from that directory, c:\Mytest\, after the outputs have been sent or created. Do I have to write more sub-procedures? Please assist. Thank you.

 

[1DMF]

To create an Excel Spreadsheet of a query I use.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "NameofQuery, "FullPathToExcelFile\FileName.xls", True

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[1DMF]

oops missed a quote mark

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "NameofQuery[b]"[/b], "FullPathToExcelFile\FileName.xls", True

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[Tamrak]

Hi 1DMF,

Thank you. I also found out that I have to type True, "", 0 at the end to make it work. Thanks for your response.

DoCmd.OutputTo acOutputQuery, "QryTest1", acFormatXLS, strP1, True, "", 0

 

[1DMF]

cool , glad you got it sorted!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.