Select and Import delimited data file (ERA)

[Troutwater]

I have begun to chase my tail on this one, so help me out.

I need to summarize some data from multiple files but I can't seem to have the user select the file and import it.

It's a ERA(electronic remittance advice) 835 file that will import with the "Import Text File" routine. I've added the "GetOpenFileName" to it but apparently have not appropriately adjusted my "QueryTable.Add" routine.

I removed the .Refresh Backgroundquery:=False line as this was causing an error and this not SQL data.

The desired files have names like this: C123456.835.EDIPROCESSERPROCEEDED

My simpleton code reads:

Sub IMPORT_835()
' IMPORT_835 Macro
Dim InputFile As Variant

InputFile = Application.GetOpenFilename(Title:="Choose your file", _
FileFilter:="All Files (*.*), *.*")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Inputfile", _
Destination:=Range("$B$2"))

.Name = InputFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "*"
.TextFileColumnDataTypes = Array(2, 2, 2, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True

End With
End Sub

NO DATA IS IMPORTED, WHY?

 

[Andrew Poulsom]

Try:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Inputfile, _
Destination:=Range("$B$2"))

 

[Troutwater]

It's still not responding:

Sub IMPORT_835()
' IMPORT_835 Macro
Dim InputFile As Variant

InputFile = Application.GetOpenFilename(Title:="Choose your file", _
FileFilter:="All Files (*.*), *.*")

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & InputFile, Destination:=Range("$B$2"))
.Name = InputFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "*"
.TextFileColumnDataTypes = Array(2, 2, 2, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True

End With

 

[Andrew Poulsom]

Try putting back in:

.Refresh BackgroundQuery:=False

 

[Troutwater]

Nope, that returned the error message:

Run-time error '1004':
Application-defined or object-defined error

 

[Andrew Poulsom]

What does the macro recorder give you if you import the file manually?

 

[Troutwater]

Yes, I had the macro recorder on for this code. So the macro recorder puts the file name "hard-coded" in and imports the file without any problems.

I only added the openfilename routine and dim(ed) the varibles. But for whatever reason it just is not importing the file. Is a 835 data file recognized another way?

 

[Troutwater]

In case you meant the actual code:

Sub Import_Report()
'
' Import_Report Macro
'
'
Sheets("Sheet3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;R:\EDI\Input\Medicare\Archive\c1305420.835.EDIProcessorProcessed", _
Destination:=Range("$B$2"))
.Name = "c1305420.835.EDIProcessorProcessed"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "*"
.TextFileColumnDataTypes = Array(1, 2, 1, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Andrew Poulsom]

Are you able to run that recorded macro? If so this should work:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & InputFile, _

 

[Troutwater]

Well given it is not yet working,

maybe the ".Refresh Backgroundquery:=False" is the problem.

When recording the macro this line is included but when I try to run that some code again, this line fells with:

Run-time error '1004':
Application-defined or object-defined error


Research indicates this line is only for SQL data imports, so that's why I haven't thought this was the problem.