Variables in CSV Import

[Nighabi]

I attempting to use vba to import 2 different CSV files. The files are almost identical so I am trying to reuse as much of the code as possible by using variables in the settings. The setting that seems to be causing me problems is the
.TextFileColunmDataTypes = Array(DataArray) where I defined the variable DataArray as String and the value is set based on file type as follows.
DataArray = "2, 2, 9, 9, 9, 2, 2, 9, 9, 1, 9, 2, 2, 2"
In testing I receive an error of
Run-time error '5':
Invalid precedure call or argument

 

[Joe4]

I would recommend using the Macro Recorder, and record yourself importing the two CSV files manually. When finished you can compare the resulting VBA code from the two with each other and also compare it to your original code to see if anything different sticks out.

 

[Nighabi]

That was how I got the original code. The issue seems to be that the original code said
.TextColumnDataTypes = Array(1,2,2,2)
I replaced the 1,2,2,2 with a variable
Public ImportArray As String
ImportArray="1,2,2,2"
That makes my new line read
.TextColumnDataTypes = Array(ImportArray)
This errors out

 

[Joe4]

Yes, apparently you cannot do that with Arrays in VBA (declare them like a variable like that). I have to confess that I do not use Arrays a whole lot in VBA.

What are you hoping to gain by doing this?

Note that this works:

    Dim ImportArrary As Variant
    ImportArray = Array(1, 2, 2, 2)
...
    .TextColumnDataTypes = ImportArray

 

[Nighabi]

I was hoping to re use as much code as I could in some macro's I am creating. I have two different files I am importing, each one with a different import schema. To handle this I assign variables based on file layout. This allows me to use the same import code just filling out range name and Data Array with the assigned variables.
Thanks for the answer, I will test that and see if it solves my issue

 

[Joe4]

In your first message, you mentioned that the files were almost identical. If that is the case, where the ImportArrays for both are exactly the same, then there isn't really anything to be gained by storing the import array in a variable (if it isn't changing).

 

[Nighabi]

key word there is 'almost' one file has 2 additional fields and the order is different so skipping fields and text fields etc are in a different order.

 

[Joe4]

Well, see what happens. Arrays can be tricky things to work with, especially if you need to ReDim them. I am certainly no expert on them, so can't give you too much advice on them.

In the end, if it gets too frustrating, you may find it easier simply to have two import templates, one for each file.