0

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?

Erik A
  • 31,639
  • 12
  • 42
  • 67
user793468
  • 4,898
  • 23
  • 81
  • 126
  • See http://stackoverflow.com/questions/9468488/writing-a-query-output-to-text-file-using-filesystemobject – Fionnuala Feb 27 '12 at 21:20

1 Answers1

1

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Filesystemobject and user793468 : http://stackoverflow.com/questions/9468488/writing-a-query-output-to-text-file-using-filesystemobject http://stackoverflow.com/questions/9470232/invalid-procedure-call-or-agrument http://stackoverflow.com/questions/9442215/reading-and-writing-a-csv-file-using-filesystemobject/9442846#9442846 – Fionnuala Feb 28 '12 at 14:35
  • 1
    The other 2 questions each consisted of a single sentence. In this one, the OP showed us code, described an error, and identified the line which triggered it. I want to encourage this type of question. So I attempted an answer. – HansUp Feb 28 '12 at 16:25