• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1196
  • Last Modified:

Use Excel Automation from MSAccess VBA to Import a Text File and Save as Workbook

I have a text file that has been importing via Excel with VBA and gets saved as a workbook. I want to do the same with automation in MSAccess. I am getting a compile error on the Workbooks.OpenText method: Expected function or variable. Can you help me with the syntax? I have attached the txt file to import and the Access accdb that needs to run the automation.

Here is the code that works in Excel VBA:
Private Sub ImportTextFile1()

'Import order data
Workbooks.OpenText Filename:="C:\Dev\OrderTest.txt", Origin:=936, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(4, _
        2), Array(12, 2), Array(32, 2), Array(34, 1), Array(48, 1), Array(60, 1), Array(76, 1), _
        Array(84, 1), Array(102, 2), Array(112, 2), Array(121, 2), Array(129, 2)), _
        TrailingMinusNumbers:=True

'Save the file
ActiveWorkbook.SaveAs Filename:= _
        "C:\Dev\Order.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:=""

End Sub
TestDB.accdb
OrderTest.txt
0
venture-it
Asked:
venture-it
1 Solution
 
Saqib Husain, SyedEngineerCommented:
You have assigned excel zone. Do you need any help with excel?
0
 
peter57rCommented:
Modify as follows to use in Access...(assumes Excel is not running)

Private Sub ImportTextFile1()

Dim xlApp As Object
Const xlfixedwidth = 2    
Const xlnormal = -4143
Set xlApp = CreateObject("Excel.Application")


'Import order data
xlApp.Workbooks.OpenText FileName:="C:\Dev\OrderTest.txt", Origin:=936, _
        StartRow:=1, DataType:=xlfixedwidth, FieldInfo:=Array(Array(0, 1), Array(4, _
        2), Array(12, 2), Array(32, 2), Array(34, 1), Array(48, 1), Array(60, 1), Array(76, 1), _
        Array(84, 1), Array(102, 2), Array(112, 2), Array(121, 2), Array(129, 2)), _
        TrailingMinusNumbers:=True

'Save the file
xlApp.ActiveWorkbook.SaveAs FileName:= _
        "C:\Dev\Order.xls", _
        FileFormat:=xlnormal, Password:="", WriteResPassword:=""


xlApp.Quit
Set xlApp = Nothing

End Sub
0
 
venture-itAuthor Commented:
Thanks, this is exacly what I needed.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now