Array Statements

[krismcmillan]

Evening All

I'm trying to import an .rtf file (as per example below) into MS Access 2010, using VBA.


Owner Location Type Product Code Product Description/Desc 2 Pack Size Rotation No. H A Cases/ Units

E45 AA/09A/1 Pick 00271296 PU VALUE BINA RED ASSTD 0/ 0
E45 AA/09A/1 Pick 00319997 2B FASHIONJOGLEG BLU/GRY ASSTD 0/ 0
E45 AA/09A/1 Pick 00371296 PU VALUE BINA RED ASSTD 0/ 0
E45 AA/09A/1 Pick 00372379 PU VALUE BALLERINA BLACK ASSTD 0/ 0
E45 AA/09A/1 Pick 00471296 PU VALUE BINA RED ASSTD 0/ 0

I have managed to write the code below and it imports the first four columns into a access table, which is great.

Workbooks.OpenText FileName:="C:\Users\kmcmilla\Desktop\PFK.rtf", Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9) _
, Array(4, 1), Array(9, 1), Array(22, 1), Array(31, 2), Array(53, 9), Array(84, 1)), _
TrailingMinusNumbers:=True


But I need to import the rest of the file, into a table. But not sure on how to adjust the Array statements to accommodate this.

Thanks

 

[Joe4]

[COLOR=#333333]Workbooks.OpenText FileName:="C:\Users\kmcmilla\Desktop\PFK.rtf", Origin:= _[/COLOR]
[COLOR=#333333]xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9) _[/COLOR]
[COLOR=#333333], Array(4, 1), Array(9, 1), Array(22, 1), Array(31, 2), Array(53, 9), Array(84, 1)), _[/COLOR]
[COLOR=#333333]TrailingMinusNumbers:=True[/COLOR]

Where did you get that code from?

That looks like code for importing it into Excel, not Access (note the Workbooks object name).

 

[krismcmillan]

I got the code from another Access database, that I have written.

What should I use instead for "Workbooks.OpenText" code?

 

[Joe4]

Is that other Access database calling/setting Excel objects? Do you see any Excel references in that VBA code?

The Access equivalent is "TransferText" using an Import Specification, which is basically an import map (see: https://msdn.microsoft.com/en-us/library/office/ff835958.aspx).

Creating the Import Specification is actually quite easy. You just do the import once manually (External Data menu -> Import -> Text File), which invokes the Import Wizard. You then indicate where all the field breaks are (looks like a Fixed Width file), and then in your last step before completing the import, click the Advanced button, and save your Import Specification name. You can then use it in your Imports.