VBA to export multiple tables to .txt files

[dreid1011]

Good morning,

I have a macro that I converted to VB so I can export multiple tables to individual .txt files.

Here's what I have:

Function Quarterly_Export_Macro()
On Error GoTo Quarterly_Export_Macro_Err
    Dim strName() As Variant
    Dim i As Integer
    strName = Array("MS_ACCOUNT", "[COLOR="#FF0000"][/COLOR][B]MS_EXEMPTIONS[/B]", "MS_FLOORS", "MS_INVENTORY", "MS_NOTATIONS", "MS_SPECIAL_ASSESSMENT", "MS_VALUE_SUMMARY", "ORCATS_NAMES", "P_ACCOUNT", "P_VALUES", "R_FLOOR_INV", "R_IMPR_ACC", "R_SPECIAL_ASSESSMENT", "REAL_ACCOUNT", "REAL_DEAD_NUMBERS", "REAL_EXEMPTIONS", "REAL_IMPROVEMENTS", "REAL_LAND_ADJUSTMENTS", "REAL_LAND_SIZES", "REAL_LAST_SALE", "REAL_LEGAL", "REAL_MAP_XREF", "REAL_NOTATIONS", "REAL_SALES", "REAL_SALES_ACCOUNTS", "REAL_SITUS", "REAL_VALUE_SUMMARY", "REAL_VALUES", "TAX_TOTALS", "TAXES_DELINQUENT", "tblFIELD_DESCRIPTIONS", "U_ACCOUNT", "U_VALUES")
    
    For i = 0 To 33
    DoCmd.TransferText acExportDelim, "QExportSpecs", strName(i), "F:\Quarterly Exports\" & strName(i) & ".txt", True
    Next
    
Quarterly_Export_Macro_Exit:
    Exit Function

Quarterly_Export_Macro_Err:
    MsgBox Error$
    Resume Quarterly_Export_Macro_Exit

End Function

I've set up an array for all the table names, and that seems to work fine. It gets through the first table, and then I receive an error stating that my second table(bold entry above) does not exist, but it's not the correct name in the error.

Imgur

Any help is much appreciated, thanks!

 

[Joe4]

For one thing, you only have 33 items in your array, so it should be:

For i = 0 to 32

(remember, it is zero based!)

Actually, it is usually better to use this:

For i = Lbound(strName) to Ubound(strName)

Then you don't need to worry about counting them, which is great if you are ever adding/subtracting values from your array.

 

[dreid1011]

For one thing, you only have 33 items in your array, so it should be:

For i = 0 to 32

(remember, it is zero based!)

Ah, I missed one. Thanks for catching that.

Actually, it is usually better to use this:

For i = Lbound(strName) to Ubound(strName)

Then you don't need to worry about counting them, which is great if you are ever adding/subtracting values from your array.

I will try that, thanks.


Edit: No good, I am still receiving the error. Here's a link to the error http://imgur.com/TCe2ey7.

 

[dreid1011]

Ok, I did some testing, and it seems so far that only the first array entry works. It returns the error on "MS_EXEMPTIONS" after it processes "MS_ACCOUNT". If I remove "MS_ACCOUNT", it still returns an error on "MS_EXEMPTIONS". If I remove both of those, then it returns an error on "MS_FLOORS". So, it looks like it is recognizing the first table, but not the others? I have been over the spelling, and they are the same as the table names. I'm stumped.

 

[Joe4]

Quick question. Are all your tables the exact same table structure?
If not, I doubt you can use the same Export Specification for all of them.

 

[dreid1011]

Quick question. Are all your tables the exact same table structure?
If not, I doubt you can use the same Export Specification for all of them.

I'm not sure, I did not make the db. I've only performed queries and manually exported tables up to this point. As far as structure, can you be more specific? I apologize if I sound like an Access noob

 

[Joe4]

Well, your array holds your 33 table names, right?
If you open each table in Design View, do they ALL have the exact same number of fields and the exact same field names? If not, I think you will have a problem.

The second argument of the TransferText Action is the Specification name (see: https://msdn.microsoft.com/en-us/library/bb177406(v=office.12).aspx).
This is the export "map" for the data, i.e. tells it what to use as a delimiter, whether to use text qualifiers, what the format of each field to export is, etc. The export specification name in your code is ""QExportSpecs". If your tables are not of the same structure, you can probably not use the same Export Specification on them all.

One way to test is to run some code specifically on your second table and see if it works:

DoCmd.TransferText acExportDelim, "QExportSpecs", "MS_EXEMPTIONS", "F:\Quarterly Exports\MS_EXEMPTIONS.txt", True

Just try creating a new form, putting a command button on your form, and putting that code inside of the "On Click" event of that button and see it it works. Does it create the MS_EXEMPTIONS export file for you?

 

[dreid1011]

Well, your array holds your 33 table names, right?
If you open each table in Design View, do they ALL have the exact same number of fields and the exact same field names? If not, I think you will have a problem.

The second argument of the TransferText Action is the Specification name (see: https://msdn.microsoft.com/en-us/library/bb177406(v=office.12).aspx).
This is the export "map" for the data, i.e. tells it what to use as a delimiter, whether to use text qualifiers, what the format of each field to export is, etc. The export specification name in your code is ""QExportSpecs". If your tables are not of the same structure, you can probably not use the same Export Specification on them all.

One way to test is to run some code specifically on your second table and see if it works:

DoCmd.TransferText acExportDelim, "QExportSpecs", "MS_EXEMPTIONS", "F:\Quarterly Exports\MS_EXEMPTIONS.txt", True

Just try creating a new form, putting a command button on your form, and putting that code inside of the "On Click" event of that button and see it it works. Does it create the MS_EXEMPTIONS export file for you?

Ok, I see now. I thought that's what you meant, but I wasn't sure. No, they are not all the same. Now that you mention it, I did save that specification specifically on the first table. And I have already tested on the second (and third) tables, both not working. I suppose I could just make another array with a spec for each table. Doing it all now will save time, as I export the tables for multiple customers at different times of the year. I will try that and report here with results. Thanks!

 

[Joe4]

You could follow the same logic and incorporate your table name into the specification name. Like if you named then all "X" then your table name, your could use:

"X" & strName(i)

as your dynamic Specification Name.

 

[dreid1011]

You could follow the same logic and incorporate your table name into the specification name. Like if you named then all "X" then your table name, your could use:

"X" & strName(i)

as your dynamic Specification Name.

I see, yes we're on the same idea here. I am saving them with the same name but a different number at the end.