Solved

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

Posted on 2013-01-28
3
1,092 Views
Last Modified: 2013-01-28
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
Comment
Question by:venture-it
3 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38825933
You have assigned excel zone. Do you need any help with excel?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 38826024
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
 

Author Closing Comment

by:venture-it
ID: 38829371
Thanks, this is exacly what I needed.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question