Use VBA to copy contents of edi file

[armywalrus2]

Hello. I would like to be able to copy the contents of multiple edi 834 files into one workbook, one worksheet. I can then use text to columns to get the data I need. The issue is I have a year's worth of files to go through and I don't want to manually open them all and copy the data. I have code from here that I use for Excel files, but it refers to specific worksheets and ranges of data - here I just want to dump the contents of each edi file into their own cell. Any assistance would be appreciated. Thank you!

 

[armywalrus2]

Please forgive the formatting it looks better in Excel.

Below is what I use -which I got from here- to copy data from multiple excel files into one worksheet. I am not sure if it is possible to adapt this to work for edi files. The part of this I don't know how to replace is the part telling it what to copy. I can update the folder path and file extension, but not sure what, if anything, can be placed where this part is

LastRow = .Sheets("owssvr").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

.Sheets("owssvr").Range("A1:Y200").Copy



Entire code for xls files:

Sub refund_logs()


'grabs refund log data in order to compare transaction IDS to payments showing in HIP Viewer,

'in order to identify payments which have already been refunded


'loop through files in a folder, copy and paste data into new Excel workbook



' Declare variables

Dim folderPath As String

Dim Filename As String

Dim wkbDest As Workbook

Dim wkbSource As Workbook

Set wkbDest = ThisWorkbook

Dim LastRow As Long


' Optimize macro speed

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual


' Set variable for folder path

folderPath = "H:\Refund_Logs"


If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"


' Set variable equal to the name of any excel file

Filename = Dir(folderPath & "*.xls*")


' Do While loop: for each workbook in specified folder, while filename is not blank, opens and copies data, pastes into Master, then closes each file

Do While Filename <> ""

Set wkbSource = Workbooks.Open(folderPath & Filename)

With wkbSource

LastRow = .Sheets("owssvr").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

.Sheets("owssvr").Range("A1:Y200").Copy wkbDest.Sheets("Already_Refunded_Logs").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

.Close savechanges:=False

End With

Filename = Dir

Loop


ResetSettings:

'Reset Macro Optimization Settings

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True


End Sub