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

Export search results from Form to CSV (macro)

P: 9
I have a search form that returns records to another form, rather than a table. Here are my questions:

1) Is it possible to export the form results (as opposed to query results) to CSV? I've been successful at exporting a table of results before, but not a form.

2) If so, can this be done via macro?

3) If a macro is possible, can the macro further allow the user to choose location and filename when exporting?

I've tried a straightforward approach of pointing the macro to the form, but it appears that the macro only wants a table or query. My guess is that I will have to build a further query 'behind the scenes' in order to export...

Thanks,

-Scott
Feb 18 '07 #1
Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,601
I have a search form that returns records to another form, rather than a table. Here are my questions:

1) Is it possible to export the form results (as opposed to query results) to CSV? I've been successful at exporting a table of results before, but not a form.

2) If so, can this be done via macro?

3) If a macro is possible, can the macro further allow the user to choose location and filename when exporting?

I've tried a straightforward approach of pointing the macro to the form, but it appears that the macro only wants a table or query. My guess is that I will have to build a further query 'behind the scenes' in order to export...

Thanks,

-Scott
1. Form results cannot be transfered to a CSV File. In order to Export data to a CSV File, you use the TransferText() Method and this Method requires either a Table or Query Argument, not an SQL Statement. You must first create a query and then specify the name of the query in the Table Name argument. All is not lost however, because if your Form results are generated by changing the RecordSource of the Form, you can programmatically create the CSV File by the following code which creates a Recordset based on the Form's RecordSource and writes selected Fields in a CSV Format to CSV.txt in the Test Directory. Field names are my own for testing purposes.
Expand|Select|Wrap|Line Numbers
  1. Dim MySQL As String, MyDB As Database, MyRS As Recordset
  2.  
  3. MySQL = Me.RecordSource
  4.  
  5. Open "C:\Test\CSV.txt" For Output As #1
  6.  
  7. Set MyDB = CurrentDb()
  8. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
  9.   Do While Not MyRS.EOF
  10.     Write #1, MyRS![EmployeeID], MyRS![FirstName], MyRS![LastName]
  11.     MyRS.MoveNext
  12.   Loop
  13. MsgBox MyRS.RecordCount
  14.  
  15. Close #1
  16. MyRS.Close
If you are further interested, please let me know. I'm sure the other Experts/ Moderators will come up with other solutions, probably better. Good Luck!
Feb 19 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Actually, that is an excellent solution, one variation I would try (borrowing ADezii's code) is ...

Expand|Select|Wrap|Line Numbers
  1. Dim MyRS As Recordset
  2.  
  3. Open "C:\Test\CSV.txt" For Output As #1
  4.  
  5. Set MyRS = Me.RecordsetClone
  6. MyRS.MoveFirst
  7. Do While Not MyRS.EOF
  8.     Write #1, MyRS![EmployeeID], MyRS![FirstName], MyRS![LastName]
  9.     MyRS.MoveNext
  10.   Loop
  11. MsgBox MyRS.RecordCount
  12.  
  13. Close #1
  14. MyRS.Close
I'm not sure if RecordsetClone will return the filtered records but I think it will.

Mary
Feb 19 '07 #3

ADezii
Expert 5K+
P: 8,601
Actually, that is an excellent solution, one variation I would try (borrowing ADezii's code) is ...

Expand|Select|Wrap|Line Numbers
  1. MyRS As Recordset
  2.  
  3. Open "C:\Test\CSV.txt" For Output As #1
  4.  
  5. Set MyRS = Me.RecordsetClone
  6. MyRS.MoveFirst
  7. Do While Not MyRS.EOF
  8.     Write #1, MyRS![EmployeeID], MyRS![FirstName], MyRS![LastName]
  9.     MyRS.MoveNext
  10.   Loop
  11. MsgBox MyRS.RecordCount
  12.  
  13. Close #1
  14. MyRS.Close
I'm not sure if RecordsetClone will return the filtered records but I think it will.

Mary
You were right on with the RecordsetClone approach, thanks. No sense playing around with the original, when you can use a copy!
Feb 19 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
You were right on with the RecordsetClone approach, thanks. No sense playing around with the original, when you can use a copy!
LOL - My thoughts exactly.
Feb 19 '07 #5

P: 9
Trying Mary's mods, I am getting an error: File Already Open. The code is attached to a button on the results form.

I've checked to see if the CSV file actually is open, but it ain't. This happens even after closing and reopening the db. I've also tried changing the location and just deleting the file, but that results in an Object Required notice as expected.

I also have a problem with the As Database declaration throwing another error about invalid user-defined type. Commenting it out yields the above results. Did I misunderstand how to declare that variable?
Feb 19 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Trying Mary's mods, I am getting an error: File Already Open. The code is attached to a button on the results form.

I've checked to see if the CSV file actually is open, but it ain't. This happens even after closing and reopening the db. I've also tried changing the location and just deleting the file, but that results in an Object Required notice as expected.

I also have a problem with the As Database declaration throwing another error about invalid user-defined type. Commenting it out yields the above results. Did I misunderstand how to declare that variable?
It's not included in my mods

try adding this line of code to the beginning of the code before the 'Open' statement.

Expand|Select|Wrap|Line Numbers
  1. Close #1
Feb 19 '07 #7

P: 9
Sorry Mary - my response implied incorrectly that it was part of your example. I discovered it wasn't appropriate to the code, so have since removed it.

I've also tried using Close in the beginning of the code, but that now gives a Type Mismatch error. I guess that means there's something wrong with the variable declaration, but I don't see how using RecordsetClone would violate strict typing. Here's my current code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintToCSV_Click()
  2. On Error GoTo Err_cmdPrintToCSV_Click
  3.  
  4. Dim MyRS As Recordset
  5.  
  6. Close #1
  7.  
  8. Open "e:\Documents and Settings\profile\Desktop\CSV.txt" For Output As #1
  9.  
  10. Set MyRS = Me.RecordsetClone
  11. MyRS.MoveFirst
  12.   Do While Not MyRS.EOF
  13.     Write #1, MyRS![cardTitle]
  14.     MyRS.MoveNext
  15.   Loop
  16. MsgBox MyRS.RecordCount
  17.  
  18. Close #1
  19. MyRS.Close
  20.  
  21. Exit_cmdPrintToCSV_Click:
  22.     Exit Sub
  23.  
  24. Err_cmdPrintToCSV_Click:
  25.     MsgBox Err.Description
  26.     Resume Exit_cmdPrintToCSV_Click
  27.  
  28. End Sub
  29.  
Note that I am currently only trying to record one element from the form, [cardTitle]. Eventually, there will be about 10 elements.
Feb 19 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry Mary - my response implied incorrectly that it was part of your example. I discovered it wasn't appropriate to the code, so have since removed it.

I've also tried using Close in the beginning of the code, but that now gives a Type Mismatch error. I guess that means there's something wrong with the variable declaration, but I don't see how using RecordsetClone would violate strict typing. Here's my current code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPrintToCSV_Click()
  2. On Error GoTo Err_cmdPrintToCSV_Click
  3.  
  4. Dim MyRS As Recordset
  5.  
  6. Close #1
  7.  
  8. Open "e:\Documents and Settings\profile\Desktop\CSV.txt" For Output As #1
  9.  
  10. Set MyRS = Me.RecordsetClone
  11. MyRS.MoveFirst
  12.   Do While Not MyRS.EOF
  13.     Write #1, MyRS![cardTitle]
  14.     MyRS.MoveNext
  15.   Loop
  16. MsgBox MyRS.RecordCount
  17.  
  18. Close #1
  19. MyRS.Close
  20.  
  21. Exit_cmdPrintToCSV_Click:
  22.     Exit Sub
  23.  
  24. Err_cmdPrintToCSV_Click:
  25.     MsgBox Err.Description
  26.     Resume Exit_cmdPrintToCSV_Click
  27.  
  28. End Sub
  29.  
Note that I am currently only trying to record one element from the form, [cardTitle]. Eventually, there will be about 10 elements.
The code is fine. At a guess I would say you have no DAO library ticked. In the VBA Editor window go to Tools - References and make sure there is a Microsoft DAO Library ticked on the list.

Mary
Feb 19 '07 #9

P: 9
Thanks again, Mary, but still getting the type mismatch error. I did not have a DAO reference, so I tried each of the 3 available in turn. None gave me a different result. In desperation, I closed everything and recompiled, but of course that did nothing.

Could the error have something to do with my form or tables? Or is it most likely something in the VBA script?
Feb 19 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks again, Mary, but still getting the type mismatch error. I did not have a DAO reference, so I tried each of the 3 available in turn. None gave me a different result. In desperation, I closed everything and recompiled, but of course that did nothing.

Could the error have something to do with my form or tables? Or is it most likely something in the VBA script?
Tick the DAO reference with the highest version number and then use the up arrow to move it to the highest point on the list it will go. This will probably be around position 3. Then try again ...

Mary
Feb 19 '07 #11

ADezii
Expert 5K+
P: 8,601
Thanks again, Mary, but still getting the type mismatch error. I did not have a DAO reference, so I tried each of the 3 available in turn. None gave me a different result. In desperation, I closed everything and recompiled, but of course that did nothing.

Could the error have something to do with my form or tables? Or is it most likely something in the VBA script?
You may have References set to both DAO and ADO Libraries. In that case you may to to explicitly Declare the Object Variables as in:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Feb 20 '07 #12

P: 9
Hmm... that worked on my desktop, but not on my laptop. Both are running the same install, afaik.

No biggie. As long as one of them works, I can get what I need.

I've changed from Write # to Print # to get tab-delimited instead. Other than that, I think this part is good to go!

Thanks very much =)

**edit**

I hadn't seen ADezii's post when I wrote this. I'll try the explicit declarations on my laptop in a bit. Again, I really appreciate the help.
Feb 20 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Hmm... that worked on my desktop, but not on my laptop. Both are running the same install, afaik.

No biggie. As long as one of them works, I can get what I need.

I've changed from Write # to Print # to get tab-delimited instead. Other than that, I think this part is good to go!

Thanks very much =)
You're welcome.

BTW, did you catch ADezii's post as you both posted very close together. It might solve the laptop issue.

Mary
Feb 20 '07 #14

Post your reply

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