Export to txtfile

[KEA66]

Hi all.
I've tried to Macro and VBA but my computer keeps telling me I'm doing something wrong.
I have 30+ queries (or table columns) I wish to export to txtfile files that I need to be able to replace each time the table and / or queries are updated.

This is the code I've used but when I run the macro the error message is that the FreeFile is not a valid outside procedure. The code debugs without issues up to but not including the macro.

Any suggestions would be greatly appreciated

Option Compare Database

Dim fFile As Long
Dim strFile As String
Dim strString As String
Dim Rs As Recordset
Dim RsSql As String

fFile = FreeFile
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldAddComp.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldEstate.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldLotNo.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldPstCde.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldRPSP.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldState.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldStreet.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldStrNo.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryBldSub.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl1andCl2FlNmes.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl1FstNme.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl1FullNme.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl1HmePh.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl1LstNme.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl1MobPh.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl1Ttl.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl2FstNme.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl2FullNme.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl2HmePh.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl2LstNme.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl2MobPh.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryCl2Ttl.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryClNmesandPstlAdd.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryHseDsgn.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryLclAuth.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryPackage.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryPostalAddress.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryPstlAddLne1.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryPstlCde.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryPstlSte.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryPstlSub.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qrySpecification.txt"
strFile = "C:\Users\Desktop\New DataBase\txtfilesforforms\qryWindrating.txt"

Set Rs = CurrentDb.OpenRecordset(RsSql)

If Not Rs.EOF And Not Rs.BOF Then
Open strFile For Output As #fFile
Do Until Rs.EOF

For i = 0 To Rs.Fields.Count - 1
strString = strString & Rs(i) & vbTab
Next
Print #fFile, strString
strString = ""
Rs.MoveNext
Loop
Rs.Close
Close #fFile
End If
Sub mcrsveToTxt()
End Sub

 

[plog]

What is Freefile? You are setting its value to fFile which you previously declared as a number. So what number is Freefile? Is it a function that returns a value? Is it something that is passed to the function?

What you posted seems like you've left a lot out. RsSQL is never assigned a value in what you posted, but you use it to open a recordset somehow. All those strFile assignments immediately get undone with the next line of code.

Maybe I'm missing something, but it might be time to start from square one--tell us what you are trying to do using the least amount of jargon or coding terms possible.

 

[JHB]

.... I run the macro the error message is that the FreeFile is not a valid outside procedure.

You are using "FreeFile" outside a procedure, you need put it into a Sub/End Sub.

Why do you have the long list with "strFile = ..."?
strFile will always contain
"C:\Users\Desktop\New DataBase\txtfilesforforms\qryWindrating.txt"
when you get to use it.

 

[KEA66]

Hi
Each strFile is a seperate query I want to export to txt., so I guess I've got it all back to front and upside down - the joys of being a learner huh.
To be honest I have no idea what I'm doing. I cant get anything to work and because I need to automate the procedure so that after each and every update the txt files are updated too, the nice simple little button which works perfectly isn't enough.

 

[JHB]

Hi
Each strFile is a seperate query I want to export to txt.

No strFile is not a query, it is only a variable contaning at string

To be honest I have no idea what I'm doing. I cant get anything to work ...

I do agree with you, you have no idea what you are doing, (and you'll never get it to work as it is).

Could you explain where the data come from and why do you want to export to a text file?
And maybe post your database with sample data, (zip it).

 

[KEA66]

I'm not trying to export to excel, I'm trying to export to text files so I can then import to PDF. I need them as seperate files to fill combo boxes in the PDF. I have the PDFs ready and waiting. I have already done it with the one step export to text and it works without a problem. What I want to do is automate the procedure so that everything just runs when there's a new entry. Thanks heaps for your time on this, I really do appreciate it

 

[JHB]

It is designed so now to create a text file for each query in the database, it is at least how I understand you want it.
I've added a button on the form with the caption "Run the export", click at it and the above happens.

At the risk of sounding condescending, but if you want to make programming in the future, purchase or borrow some beginning books about programming.
And I think you should start at the level of "Hello world!"
You've started to write code outside a procedure/function, you can't do that.

 

[KEA66]

It is designed so now to create a text file for each query in the database, it is at least how I understand you want it.
I've added a button on the form with the caption "Run the export", click at it and the above happens.

At the risk of sounding condescending, but if you want to make programming in the future, purchase or borrow some beginning books about programming.
And I think you should start at the level of "Hello world!"
You've started to write code outside a procedure/function, you can't do that.

Believe me, I think I've got every book both paperback and ebook lol. If I wasn't in such a hurry or had a couple of years to study properly I would. I just got a loose grip on the javascript in the PDF when I realise I had to do programming with totally different code and I do believ my brain is frazzled. You have been an absolute CHAMPION. I'm off to have a look at what you've done. Thanks for everything, be back to tell the forums how cool you are soon

 

[KEA66]

Believe me, I think I've got every book both paperback and ebook lol. If I wasn't in such a hurry or had a couple of years to study properly I would. I just got a loose grip on the javascript in the PDF when I realise I had to do programming with totally different code and I do believ my brain is frazzled. You have been an absolute CHAMPION. I'm off to have a look at what you've done. Thanks for everything, be back to tell the forums how cool you are soon

As I said, I'd be back to tell everyone how cool you are.... YOU ARE THE GREATEST!!!