VBA Export to text file 1

[dbram]

~ VBA Export to text file

I have spent a number of hours on this problem but can't see what I'm doing wrong. Any help or direction anyone can give me will be greatly appreciated.

Table: tbl_Users
Multiple fields, but only one for this test
Field: myUsers
a 15 character text field

Debug.Print returns the correct expected information.
The code works perfectly until I try to export to a text file.

Export specification: dbUsers
works perfectly manually
is correctly in MSysImexSpecs and MSysImexColumns tables

However, I get an error trying to export to a text file in VBA:
2498 : An expression you entered is the wrong data type for one of the arguments.

'~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~

Private Sub cmd_Run2_Click()

On Error GoTo ErrorHandler

Dim myDB As Database
Dim qdfEmail As QueryDef
Dim rstEmail As Recordset
Dim strSQL As String

strSQL = "SELECT tbl_Users.myUsers "
strSQL = strSQL & "FROM tbl_Users "
strSQL = strSQL & "ORDER BY tbl_Users.myUsers;"

Set myDB = CurrentDb()
Set qdfEmail = myDB.CreateQueryDef("", strSQL)
Set rstEmail = qdfEmail.OpenRecordset(dbOpenDynaset, dbReadOnly)
rstEmail.MoveLast
rstEmail.MoveFirst

Debug.Print rstEmail.RecordCount

Do While Not rstEmail.EOF
Debug.Print rstEmail!myUsers.Value
rstEmail.MoveNext
Loop

DoCmd.TransferText acExportDelim, "dbUsers", rstEmail, "C:\myAccess\expSQL.txt"

rstEmail.Close
qdfEmail.Close
myDB.Close

Set rstEmail = Nothing
Set qdfEmail = Nothing
Set myDB = Nothing

Exit_Routine:
Exit Sub

ErrorHandler:
MsgBox Err & " : " & Err.Description
Resume Exit_Routine

End Sub

'~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~

db
~

~*~*~*~*~*~
Excederin Headache Number 2498
~*~*~*~*~*~

 

[BrainB]

I think rstEmail should have quotes -> "rstEmail".

ie. DoCmd.TransferText acExportDelim, "dbUsers", "rstEmail", "C:\myAccess\expSQL.txt"


Regards
BrianB
** Let us know if you get something that works !
================================

 

[dbram]

~ VBA Export to text file

As suggested, I tried modifying my code with quotes around the Recordset variable for the TransferText argument:

DoCmd.TransferText acExportDelim, "dbUsers", "rstEmail", "C:\myAccess\expSQL.txt"

"rstEmail" doesn't work either. This time, I got the error:
3011 : The Microsoft Jet database engine could not find the object 'rstEmail'. Make sure the object exists and that you spell its name and the path name correctly.

The tablename argument requires a string expression representing the name of an Access table or select query containing the data to be exported. A variable name, without quotes, representing a table will successfully create a text file. Perhaps this tablename argument can only take saved select queries.

My qdfEmail select query is temporary -- only exists in this VBA code behind my form. My rstEmail recordset gets populated with the records matching the qdfEmail SQL criteria. To avoid database bloat, I am not creating a temporary table with the qdfEmail data.

I can successfully pass the correct data to the Debug.Print screen. Therefore, I should be able to pass that same data to a text file. I am missing something, I just can't figure out what it is.

BrianB - thanks for the suggestion -- I do usually miss things like quotes and commas.

~ db ~


~*~*~*~*~*~
Excederin Headache Number 2498
~*~*~*~*~*~

 

[RoyVidar]

Hi!

I'm not 100% sure, but I think you've answered your own question - I also think you probably need a saved query/table as third parameter.

If you do not want to use temptbls, maybe you'd consider writing the textfile "manually"?

If that's an option, here's a quicky on using the filesystemobject (NOTE the Microsoft Scripting Runtime library needs to be checked)

[tt]dim fs as filesystemobject
dim txt as textstream

set fs=new filesystemobject
set txt=fs.createtextfile("C:\myAccess\expSQL.txt",true)

with rstEmail
do while not .eof
txt.writeline !field1 & ";" & !field2 & "....
.movenext
loop
end with
txt.close
set txt=nothing
set fs=nothing[/tt]

You'd probably use some checking for nulls, errors etc...

HTH Roy-Vidar

 

[mscallisto]

I'm not sure this is what you want but I'll submit a post anyway, it's a general purpose "save as a text file".

Someone else posted this a long while ago and I have used it many times.

This example creates a pipe delimited file but it's obvious what changes to make to ahange the delimeter to commas,tabs etc.

Sub SaveAsPipeDelimited()
Dim r, c As Integer
Open "Pipey.txt" For Output As #1
With ActiveSheet.UsedRange
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count - 1
Print #1, .Cells(r, c); "|";
Next c
Print #1, .Cells(r, .Columns.Count)
Next r
End With
Close #1

End Sub

 

[arryb]

How can i modify the above to keep text files to 65000lines. IE excel limit. Basically i need to split one huge file into many on a regular basis so each file is no more then excels limit.

Any Ideas.

 

[mscallisto]

I'm not sure which "modify the above" you're asking about but here is the code for mine:

Sub SaveAsPipeDelimited()
Dim r, c As Integer
Open "Pipey.txt" For Output As #1
With ActiveSheet.UsedRange
For r = 1 To .Rows.Count
If r - 1 >= 65536 Then
Exit For
End If
For c = 1 To .Columns.Count - 1
Print #1, .Cells(r, c); "|";
Next c
Print #1, .Cells(r, .Columns.Count)
Next r
End With
Close #1
End Sub

 

[RoyVidar]

And this would be mine:

[tt]Sub testwrite()
Dim fs As FileSystemObject
Dim txt As TextStream
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim lCounter As Long
Dim lFileCounter As Long
Const csFileName = "c:\test"
Const csDelim = ";"

Set db = CurrentDb()
Set rs = db.OpenRecordset("table1", dbOpenForwardOnly)
Set fs = New FileSystemObject
With rs
lFileCounter = 0
lCounter = 65536
Do While Not .EOF
If lCounter = 65536 Then
If lFileCounter > 0 Then
txt.Close
End If
lFileCounter = lFileCounter + 1
Set txt = fs.CreateTextFile(csFileName & _
lFileCounter & ".txt", True)
End If
txt.WriteLine !id & csDelim & !test & csDelim '...
lCounter = lCounter + 1
.MoveNext
Loop
.Close
txt.Close
End With
Set rs = Nothing
Set txt = Nothing
Set fs = Nothing
End Sub[/tt]

Roy-Vidar

 

[arryb]

RoyVidar,

Many Thanks for your suggestion. I think that will do the trick but my version of Access (97) doesn't recognise the FileSystemObject dimension. Any pointer as to why?

Would it be a service pack / uninstalled component issue?

Thanks in advance

Arry

 

[kamfl610]

You need to make sure your References in the VBA Editor has Microsoft Scripting Runtime checked or your FileSystemObject won't work.

 

[arryb]

When running the code as per RoyVidars post, i get an error when executing the line:

txt.WriteLine !id & csDelim & !test & csDelim

The error i get is "item not found in the collection" for both !id and !test.

Sorry to be a pain but i can't get my head around this one.

Any ideas.

Cheers

 

[RoyVidar]

kamfl610, thanx for the assistance, been offline a while...

arryb, id and test are fields in my test database, you'll need to substitute those with fields in your recordset (table)

HTH Roy-Vidar

 

[arryb]

Oh. I get ya.

Silly me. Apologies for wasting your time. I should have sussed that one out!!!

Cheers Buddie

i owe ya one.

Arry