Export search results from Form to CSV (macro)

[lightning]

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

[ADezii]

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.

Dim MySQL As String, MyDB As Database, MyRS As Recordset
 
MySQL = Me.RecordSource
 
Open "C:\Test\CSV.txt" For Output As #1
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
  Do While Not MyRS.EOF
    Write #1, MyRS![EmployeeID], MyRS![FirstName], MyRS![LastName]
    MyRS.MoveNext
  Loop
MsgBox MyRS.RecordCount
 
Close #1
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!

[MMcCarthy]

Actually, that is an excellent solution, one variation I would try (borrowing ADezii's code) is ...

Dim MyRS As Recordset
 
Open "C:\Test\CSV.txt" For Output As #1
 
Set MyRS = Me.RecordsetClone
MyRS.MoveFirst
Do While Not MyRS.EOF
    Write #1, MyRS![EmployeeID], MyRS![FirstName], MyRS![LastName]
    MyRS.MoveNext
  Loop
MsgBox MyRS.RecordCount
 
Close #1
MyRS.Close

I'm not sure if RecordsetClone will return the filtered records but I think it will.

Mary

[ADezii]

Actually, that is an excellent solution, one variation I would try (borrowing ADezii's code) is ...

MyRS As Recordset
 
Open "C:\Test\CSV.txt" For Output As #1
 
Set MyRS = Me.RecordsetClone
MyRS.MoveFirst
Do While Not MyRS.EOF
    Write #1, MyRS![EmployeeID], MyRS![FirstName], MyRS![LastName]
    MyRS.MoveNext
  Loop
MsgBox MyRS.RecordCount
 
Close #1
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!

[MMcCarthy]

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.

[lightning]

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?

[MMcCarthy]

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.

Close #1

[lightning]

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:

Private Sub cmdPrintToCSV_Click()
On Error GoTo Err_cmdPrintToCSV_Click
 
Dim MyRS As Recordset
 
Close #1
 
Open "e:\Documents and Settings\profile\Desktop\CSV.txt" For Output As #1
 
Set MyRS = Me.RecordsetClone
MyRS.MoveFirst
  Do While Not MyRS.EOF
    Write #1, MyRS![cardTitle]
    MyRS.MoveNext
  Loop
MsgBox MyRS.RecordCount
 
Close #1
MyRS.Close
 
Exit_cmdPrintToCSV_Click:
    Exit Sub
 
Err_cmdPrintToCSV_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintToCSV_Click
 
End Sub
 

Note that I am currently only trying to record one element from the form, [cardTitle]. Eventually, there will be about 10 elements.

[MMcCarthy]

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:

Private Sub cmdPrintToCSV_Click()
On Error GoTo Err_cmdPrintToCSV_Click
 
Dim MyRS As Recordset
 
Close #1
 
Open "e:\Documents and Settings\profile\Desktop\CSV.txt" For Output As #1
 
Set MyRS = Me.RecordsetClone
MyRS.MoveFirst
  Do While Not MyRS.EOF
    Write #1, MyRS![cardTitle]
    MyRS.MoveNext
  Loop
MsgBox MyRS.RecordCount
 
Close #1
MyRS.Close
 
Exit_cmdPrintToCSV_Click:
    Exit Sub
 
Err_cmdPrintToCSV_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintToCSV_Click
 
End Sub
 

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

[lightning]

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?

[MMcCarthy]

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

[ADezii]

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:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset

[lightning]

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.

[MMcCarthy]

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