I am trying to write a query output to a text file using file system object.
Here is my code so far:
Sub CreateAfile()
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
Dim objFile As Object, TextFile As Object
Set rs = CurrentDb.OpenRecordset("qryOutput")
Set objFile = CreateObject("Scripting.FileSystemObject")
Set TextFile = objFile.CreateTextFile("C:\Users\Documents\Test.txt", True)
rs.MoveFirst
Do Until rs.EOF
TextFile.WriteLine rs.Fields("field1").Value
rs.MoveNext
Loop
rs.Close
TextFile.Close
End Sub
I get an error: "Invalid procedure call or argument" At line: TextFile.WriteLine rs.Fields("field1").Value
Am i missing something here?
I don't have that folder, C:\Users\Documents, on my system. So your code gives me error #76, "Path not found", on the CreateTextFile line. Changing the folder to one which exists, and where I have full permission, allows the code to run without error. I don't understand why you're getting an error at TextFile.WriteLine.
These two lines seem contradictory.
Set rs = CreateObject("ADODB.Recordset")
Set rs = CurrentDb.OpenRecordset("qryOutput")
CurrentDb.OpenRecordset returns a DAO recordset rather than an ADO recordset. However, since rs was declared as an object, VBA doesn't care that you first assigned an ADO recordset to it before you re-assign a DAO recordset to it. I don't see how this contributes to your error, but I would discard the CreateObject("ADODB.Recordset") line anyway. Also, change the declaration for rs to:
Dim rs As DAO.Recordset
(You will need to set a reference if the compiler complains about that declaration.)
Aside from that, I still don't understand why you're getting an "Invalid procedure call or argument" error.