By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

How to loop & substitute ???

P: 36
Hi All,

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT MSysObjects.Name 
  3. FROM MSysObjects 
  4. WHERE (((MSysObjects.Type)=5)); 
  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??

Expand|Select|Wrap|Line Numbers
  1.  
  2. DoCmd.OutputTo acOutputQuery, "??",acFormatXLS, "D:\??" 
  3.  
  4. Example 
  5.  
  6. DoCmd.OutputTo acOutputQuery, "abc",acFormatXLS, "D:\abc" 
  7. DoCmd.OutputTo acOutputQuery, "xyz",acFormatXLS, "D:\xyz" 
  8.  
  9.  
May 18 '09 #1
Share this Question
Share on Google+
2 Replies


Megalog
Expert 100+
P: 378
This should work nicely for you:

Expand|Select|Wrap|Line Numbers
  1. Private Sub qryLoopToXLS()
  2.  
  3.     Dim rs As DAO.Recordset
  4.     Dim strSQL As String
  5.  
  6.     strSQL = "SELECT Name FROM MSysObjects WHERE Type=5"
  7.  
  8.     Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
  9.  
  10.     Do While Not rs.EOF
  11.         rs.MoveFirst
  12.         Do While Not rs.EOF
  13.             DoCmd.OutputTo acOutputQuery, rs!Name, acFormatXLS, "D:\" & rs!Name & ".xls"
  14.             rs.MoveNext
  15.         Loop
  16.     Loop
  17.     Set rs = Nothing
  18. 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.
May 18 '09 #2

P: 36
Hi Megalog,

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

Thanks thanks....
May 19 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.