Variables in CSV Import

Nighabi

New Member
Joined
Feb 13, 2008
Messages
34
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 13, 2008
Messages
34
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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:

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

Nighabi

New Member
Joined
Feb 13, 2008
Messages
34

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 13, 2008
Messages
34
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,558
Members
414,077
Latest member
ammylar5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top