Open Fixed Width Text File from Access

[Kaykayme]

In order to use the import/export specifications to import a text file in
excel format, I open and format the file in Excel programmatically and save
in the Excel 2007 format. This particular file has fixed width columns
instead of delimited. However one of the columns needs to be divided between
the numeric and text data. When opening this file in Excel there is a
provision to add a break at the point of division. Can this be done when
using VBA in Access 2007? I thought of using the TexttoColumns method but I
will have the same problem. There is no space, comma, tab or other delimiter
between the numeric and text data.

Sample code:

Set objXL = CreateObject("Excel.Applicaton")
objXL.Visible = True
strFile = strPathXLS & "po_text.xls"

objXL.Workbooks.OpenText FileName:=strFile, _
startrow:=5, DataType:=xlFixedWidth, textqualifier:=1, _
consecutivedelimiter:=True, Tab:=True, _
fieldinfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 9))

What does TextVisualLayout mean? Could this help?

Thanks for your help.

 

[Alex Dybenko]

Hi
you can import your text file using import wizard in access, specify
positions of columns, save import specification and use it in transfertext
method in VBA

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Kaykayme]

Thank you so much Alex. I have tried this method but I cannot open the file
using import wizard because although this is a text file it is saved in .xls
format. I tried the workaround below and it seems to do the job.

Sample code:
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
strFile = strPathXLS & "po_text.xls"

objXL.Workbooks.OpenText FileName:=strFile, _
startrow:=5, DataType:=xlFixedWidth

objXL.DisplayAlerts = False

objXL.ActiveWorkbook.ActiveSheet.Copy
objXL.ActiveWorkbook.SaveAs FileName:=strPathXLS & "po_text.xlsx",
addtomru:=True
objXL.ActiveSheet.Range("B1").Activate
objXL.ActiveCell.EntireColumn.Insert xlShiftToRight
objXL.ActiveSheet.Columns("A").TextToColumns DataType:=xlFixedWidth,
fieldinfo:=Array(Array(0, 1), Array(18, 1))
objXL.ActiveSheet.Columns("E").EntireColumn.Delete