VBA to import fixed width text file into Excel

[Holley]

Hello all, Hoping to find some assistance with a pesky task. Each week, I run a report that produced a text file. This task is going to be handed off to other individuals with limited Excel experience and need to automate as much as possible. I have been successful in prompting the user to open the file, but really running into issues with the actual import There are 17 columns with varying widths. I cannot seem to get this to work correctly.
The columns start at 1, 6, 8, 10, 18 (these are text) 32 (MDY) 41, 50 (General) 62, 71 (MDY) 80 (YMD) 87, 91, 93, 96 (Text), 100 (MDY) 109 (General).

Google found the below, which in theory would seem perfect, lets me enter the character where the break occurs along with the format, but I cannot get it to run without getting a Compile Error: Syntax error at the file location

Const xlFixedWidth = 2




Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
objExcel.Workbooks.OpenText _
    “C:\Scripts\Test.txt”,,,xlFixedWidth,,,,,,,,,Array(Array(0,1),Array(14,1),Array(32,1)))

Any assistance would be most appreciated!!

 

[rlv01]

The above code is meant for calling Excel from some other VBA environment (Word, Powerpoint, etc). For an Excel VBA macro you don't need this:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Have a look at this thread.

Workbooks.OpenText Open File Dialog

Hi. Trying to have files run the fixed-width automatically with Workbooks.opentext. The problem I'm running into is trying to get an open file dialog to open so the user can select the text file to convert. Below is what I have so far, but it stops running when the file opens to the import...

www.mrexcel.com

 

[Holley]

Have a look at this thread.

Workbooks.OpenText Open File Dialog

Hi. Trying to have files run the fixed-width automatically with Workbooks.opentext. The problem I'm running into is trying to get an open file dialog to open so the user can select the text file to convert. Below is what I have so far, but it stops running when the file opens to the import...

www.mrexcel.com

Oh my gosh!! This is just PERFECT! I was able to edit the array's to the breaks I needed and use the formats that suited. THANK YOU SO MUCH!

 

[rlv01]