Adapt Excel Macro to import fixed width files to Access

[kat50]

Hello folks,

Can someone help me adapt this macro/module (that imports fixed width files into excel) for use in Access? I have pulled in the excel library, but do not know where to go from here. Point of interest: data is not in a .txt file and the header row starts in row 3.

Thanks so much for your help!!
Sub OpenTextFile() Dim strFilename As String 'Show the open dialog and pass the path to the selected 'file to the String variable "strFilename" strFilename = Excel.Application.GetOpenFilename 'If the user cancels, exit the routine. If strFilename = "False" Then Exit Sub 'Display a message box to let us see what the variable was set to. MsgBox strFilename Const xlFixedWidth = 2 Set objExcel = CreateObject("Excel.Application")objExcel.Visible = TrueobjExcel.Workbooks.OpenText _ strFilename, , , xlFixedWidth, , , , , , , , , Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(23, 1), Array(30, 1), Array(33, 1), Array(42, 1), Array(48, 1), Array(51, 1), Array(54, 1), Array(64, 1), Array(70, 1), Array(80, 1), Array(87, 1)) End Sub

 

[DCrake]

What app are you working in?

Are you wanting to import data from Excel into Access?

Where did you source your vba from?

What does your source file look like?

David

 

[kat50]

This VB works in Excel I want to adapt it to work in Access so I do not have to have the user preform multiple steps.

My data is in a fixed width text file but it is not a .txt extension so I cannot use the transferText option in Access

Line 1 in the file is blank and these are the next 2 lines

Thanks so much!

Transactions at site #.
TRAN ACCT CARD VEH AUTH TY DATE TIME P# PR QUANTITY PRICE $TOTAL ODOM ERROR

 

[kat50]

How can I re-open this question?

Thanks so much!

 

[DCrake]

Going back to my earlier question are you going to be in Access or Excel at the point you want to transfer the data. VBA code can be different depending on the active application.

 

[kat50]

I appoligize if I have been unclear. I have multiple fixed width files their extension is DY3. I would like to open Access and have a function that performs as this excel macro does (to adapt this macro for use in access). Currently I have to open Excel run my macro, save the file, open access and use TransferSpreadsheet.

Thanks so much.