How to loop & substitute ???

[harshakusam]

Hi All,

 
SELECT MSysObjects.Name 
FROM MSysObjects 
WHERE (((MSysObjects.Type)=5));

From above sql i will get list of queries in my MDB file

Assume Output of above query is
abc
xyz
lmn

I have to SUBSTITUE the above values 1 by one in below AND LOOP THEM TILL LAST??

 
DoCmd.OutputTo acOutputQuery, "??",acFormatXLS, "D:\??" 
  
Example 
  
DoCmd.OutputTo acOutputQuery, "abc",acFormatXLS, "D:\abc" 
DoCmd.OutputTo acOutputQuery, "xyz",acFormatXLS, "D:\xyz"

[Megalog]

This should work nicely for you:

Private Sub qryLoopToXLS()

    Dim rs As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT Name FROM MSysObjects WHERE Type=5"

    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    Do While Not rs.EOF
        rs.MoveFirst
        Do While Not rs.EOF
            DoCmd.OutputTo acOutputQuery, rs!Name, acFormatXLS, "D:\" & rs!Name & ".xls"
            rs.MoveNext
        Loop
    Loop
    Set rs = Nothing
End Sub

You may want to doublecheck that none of your queries are using any illegal filename characters, and do a replace for those character types.

[harshakusam]

Hi Megalog,

Thanks a lot it works.. finally my work finished with this...

Thanks thanks....