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?

 

[krishnaoptif]

This thing i already done.. If you know about how to convert Electronic Remittance Advice (835) - ER format to excel ....that will work..

You have done simple import and replace.... when you will convert ERA text file to excel or SQL table then it will give you 252 columns... which is correct format... you can check below link file with the original 252 columns data ...
https://drive.google.com/file/d/0Bx8Ry12AXZ87eFRqTkVwWXA2WTA/edit?usp=sharing

252 Columns
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]SUBLNAME[/TD]
[/TR]
[TR]
[TD]SUBFNAME[/TD]
[/TR]
[TR]
[TD]SUBMI[/TD]
[/TR]
[TR]
[TD]SUBIDCODE[/TD]
[/TR]
[TR]
[TD]SUBADDR1[/TD]
[/TR]
[TR]
[TD]SUBADDR2[/TD]
[/TR]
[TR]
[TD]SUBCITY[/TD]
[/TR]
[TR]
[TD]SUBST[/TD]
[/TR]
[TR]
[TD]SUBZIP[/TD]
[/TR]
[TR]
[TD]SUBSEX[/TD]
[/TR]
[TR]
[TD]SUBDOB[/TD]
[/TR]
[TR]
[TD]SUBREL[/TD]
[/TR]
[TR]
[TD]SUBORDER[/TD]
[/TR]
[TR]
[TD]PATLNAME[/TD]
[/TR]
[TR]
[TD]PATFNAME[/TD]
[/TR]
[TR]
[TD]PATMI[/TD]
[/TR]
[TR]
[TD]PATADDR1[/TD]
[/TR]
[TR]
[TD]PATADDR2[/TD]
[/TR]
[TR]
[TD]PATCITY[/TD]
[/TR]
[TR]
[TD]PATST[/TD]
[/TR]
[TR]
[TD]PATZIP[/TD]
[/TR]
[TR]
[TD]PATSEX[/TD]
[/TR]
[TR]
[TD]PATMSTAT[/TD]
[/TR]
[TR]
[TD]PATDOB[/TD]
[/TR]
[TR]
[TD]PATREL[/TD]
[/TR]
[TR]
[TD]CLAIMPATCTL[/TD]
[/TR]
[TR]
[TD]CLAIMSTATUSCODE[/TD]
[/TR]
[TR]
[TD]CLAIMSTATUSDESC[/TD]
[/TR]
[TR]
[TD]CLAIMTOTALCHARGE[/TD]
[/TR]
[TR]
[TD]CLAIMTOTALPAID[/TD]
[/TR]
[TR]
[TD]PATRESPONSIBLE[/TD]
[/TR]
[TR]
[TD]FILIND[/TD]
[/TR]
[TR]
[TD]PAYERCLAIMCODE[/TD]
[/TR]
[TR]
[TD]FACILITYTYPECODE[/TD]
[/TR]
[TR]
[TD]CLAIMFREQ[/TD]
[/TR]
[TR]
[TD]CLAIMSUPPLEMENTALAMTAU[/TD]
[/TR]
[TR]
[TD]CLMADJGRPCODE[/TD]
[/TR]
[TR]
[TD]CLMADJGRPDESC[/TD]
[/TR]
[TR]
[TD]CLMADJCODE[/TD]
[/TR]
[TR]
[TD]CLMADJAMT[/TD]
[/TR]
[TR]
[TD]CLMADJQTY[/TD]
[/TR]
[TR]
[TD]CLMADJCODE1[/TD]
[/TR]
[TR]
[TD]CLMADJAMT1[/TD]
[/TR]
[TR]
[TD]CLMADJQTY1[/TD]
[/TR]
[TR]
[TD]CLMADJCODE2[/TD]
[/TR]
[TR]
[TD]CLMADJAMT2[/TD]
[/TR]
[TR]
[TD]CLMADJQTY2[/TD]
[/TR]
[TR]
[TD]CLMADJCODE3[/TD]
[/TR]
[TR]
[TD]CLMADJAMT3[/TD]
[/TR]
[TR]
[TD]CLMADJQTY3[/TD]
[/TR]
[TR]
[TD]CLMADJCODE4[/TD]
[/TR]
[TR]
[TD]CLMADJAMT4[/TD]
[/TR]
[TR]
[TD]CLMADJQTY4[/TD]
[/TR]
[TR]
[TD]CLMADJCODE5[/TD]
[/TR]
[TR]
[TD]CLMADJAMT5[/TD]
[/TR]
[TR]
[TD]CLMADJQTY5[/TD]
[/TR]
[TR]
[TD]CLMADJCODE6[/TD]
[/TR]
[TR]
[TD]CLMADJAMT6[/TD]
[/TR]
[TR]
[TD]CLMADJQTY6[/TD]
[/TR]
[TR]
[TD]MOAPERCENT[/TD]
[/TR]
[TR]
[TD]MOAMOUNTPAYABLE[/TD]
[/TR]
[TR]
[TD]MOAREMARK1[/TD]
[/TR]
[TR]
[TD]MOAREMARK2[/TD]
[/TR]
[TR]
[TD]MOAREMARK3[/TD]
[/TR]
[TR]
[TD]MOAREMARK4[/TD]
[/TR]
[TR]
[TD]MOAREMARK5[/TD]
[/TR]
[TR]
[TD]MOAPAYMENT[/TD]
[/TR]
[TR]
[TD]MOANONPAYABLE[/TD]
[/TR]
[TR]
[TD]MIACOVDVC[/TD]
[/TR]
[TR]
[TD]MIAOPOUTAMT[/TD]
[/TR]
[TR]
[TD]MIALIFPSYDAYS[/TD]
[/TR]
[TR]
[TD]MIADRGAMT[/TD]
[/TR]
[TR]
[TD]MIAREMARK1[/TD]
[/TR]
[TR]
[TD]MIASHAREAMT[/TD]
[/TR]
[TR]
[TD]MIAMSPPASSAMT[/TD]
[/TR]
[TR]
[TD]MIAPPSCAPAMT[/TD]
[/TR]
[TR]
[TD]MIAFSPDRGAMT[/TD]
[/TR]
[TR]
[TD]MIAHSPDRGAMT[/TD]
[/TR]
[TR]
[TD]MIADHSDRGAMT[/TD]
[/TR]
[TR]
[TD]MIAOLDCAPAMT[/TD]
[/TR]
[TR]
[TD]MIAIMEAMT[/TD]
[/TR]
[TR]
[TD]MIAOHSDRG[/TD]
[/TR]
[TR]
[TD]MIACOSTRPTDATCNT[/TD]
[/TR]
[TR]
[TD]MIAOFSDRG[/TD]
[/TR]
[TR]
[TD]MIAPPSCAPOUTAMT[/TD]
[/TR]
[TR]
[TD]MIAINDTEACHAMT[/TD]
[/TR]
[TR]
[TD]MIANONPAYPROAMT[/TD]
[/TR]
[TR]
[TD]MIAREMARK2[/TD]
[/TR]
[TR]
[TD]MIAREMARK3[/TD]
[/TR]
[TR]
[TD]MIAREMARK4[/TD]
[/TR]
[TR]
[TD]MIACAPEXCPAMT[/TD]
[/TR]
[TR]
[TD]DATEOFSERVICE1[/TD]
[/TR]
[TR]
[TD]PROCEDURE1[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODA1[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODB1[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODC1[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODD1[/TD]
[/TR]
[TR]
[TD]PROCBILLEDAMT1[/TD]
[/TR]
[TR]
[TD]PROCPAYAMT1[/TD]
[/TR]
[TR]
[TD]PROCREVENUECODE1[/TD]
[/TR]
[TR]
[TD]PROCUNITSPAID1[/TD]
[/TR]
[TR]
[TD]PROCUNITS1[/TD]
[/TR]
[TR]
[TD]PROCEDUREDESCRIPTION1[/TD]
[/TR]
[TR]
[TD]PROCALLOWEDAMT1[/TD]
[/TR]
[TR]
[TD]PROCPERDAYLIMITAMT1[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTIONAMT1[/TD]
[/TR]
[TR]
[TD]PROCNETBILLEDAMT1[/TD]
[/TR]
[TR]
[TD]PROCTAXAMT1[/TD]
[/TR]
[TR]
[TD]PROCTOTALBEFORETAXESAMT1[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTAMT1[/TD]
[/TR]
[TR]
[TD]PROCCOINSAMT1[/TD]
[/TR]
[TR]
[TD]PROCCOADJAMT1[/TD]
[/TR]
[TR]
[TD]PROCPROTHERAM1[/TD]
[/TR]
[TR]
[TD]PROCCOPAYAMT1[/TD]
[/TR]
[TR]
[TD]SERVICEDATEFROM1[/TD]
[/TR]
[TR]
[TD]SERVICEDATETO1[/TD]
[/TR]
[TR]
[TD]PROCPRIORAUTHNUMBER1[/TD]
[/TR]
[TR]
[TD]PROCCTLNUMBER1[/TD]
[/TR]
[TR]
[TD]PROCREMARKCODE1[/TD]
[/TR]
[TR]
[TD]PROCADJGRPCODE1[/TD]
[/TR]
[TR]
[TD]PROCADJCODE1[/TD]
[/TR]
[TR]
[TD]PROCADJAMT1[/TD]
[/TR]
[TR]
[TD]PROCADJQTY1[/TD]
[/TR]
[TR]
[TD]DATEOFSERVICE2[/TD]
[/TR]
[TR]
[TD]PROCEDURE2[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODA2[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODB2[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODC2[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODD2[/TD]
[/TR]
[TR]
[TD]PROCBILLEDAMT2[/TD]
[/TR]
[TR]
[TD]PROCPAYAMT2[/TD]
[/TR]
[TR]
[TD]PROCREVENUECODE2[/TD]
[/TR]
[TR]
[TD]PROCUNITSPAID2[/TD]
[/TR]
[TR]
[TD]PROCUNITS2[/TD]
[/TR]
[TR]
[TD]PROCEDUREDESCRIPTION2[/TD]
[/TR]
[TR]
[TD]PROCALLOWEDAMT2[/TD]
[/TR]
[TR]
[TD]PROCPERDAYLIMITAMT2[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTIONAMT2[/TD]
[/TR]
[TR]
[TD]PROCNETBILLEDAMT2[/TD]
[/TR]
[TR]
[TD]PROCTAXAMT2[/TD]
[/TR]
[TR]
[TD]PROCTOTALBEFORETAXESAMT2[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTAMT2[/TD]
[/TR]
[TR]
[TD]PROCCOINSAMT2[/TD]
[/TR]
[TR]
[TD]PROCCOADJAMT2[/TD]
[/TR]
[TR]
[TD]PROCPROTHERAM2[/TD]
[/TR]
[TR]
[TD]PROCCOPAYAMT2[/TD]
[/TR]
[TR]
[TD]SERVICEDATEFROM2[/TD]
[/TR]
[TR]
[TD]SERVICEDATETO2[/TD]
[/TR]
[TR]
[TD]PROCPRIORAUTHNUMBER2[/TD]
[/TR]
[TR]
[TD]PROCCTLNUMBER2[/TD]
[/TR]
[TR]
[TD]PROCREMARKCODE2[/TD]
[/TR]
[TR]
[TD]PROCADJGRPCODE2[/TD]
[/TR]
[TR]
[TD]PROCADJCODE2[/TD]
[/TR]
[TR]
[TD]PROCADJAMT2[/TD]
[/TR]
[TR]
[TD]PROCADJQTY2[/TD]
[/TR]
[TR]
[TD]DATEOFSERVICE3[/TD]
[/TR]
[TR]
[TD]PROCEDURE3[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODA3[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODB3[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODC3[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODD3[/TD]
[/TR]
[TR]
[TD]PROCBILLEDAMT3[/TD]
[/TR]
[TR]
[TD]PROCPAYAMT3[/TD]
[/TR]
[TR]
[TD]PROCREVENUECODE3[/TD]
[/TR]
[TR]
[TD]PROCUNITSPAID3[/TD]
[/TR]
[TR]
[TD]PROCUNITS3[/TD]
[/TR]
[TR]
[TD]PROCEDUREDESCRIPTION3[/TD]
[/TR]
[TR]
[TD]PROCALLOWEDAMT3[/TD]
[/TR]
[TR]
[TD]PROCPERDAYLIMITAMT3[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTIONAMT3[/TD]
[/TR]
[TR]
[TD]PROCNETBILLEDAMT3[/TD]
[/TR]
[TR]
[TD]PROCTAXAMT3[/TD]
[/TR]
[TR]
[TD]PROCTOTALBEFORETAXESAMT3[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTAMT3[/TD]
[/TR]
[TR]
[TD]PROCCOINSAMT3[/TD]
[/TR]
[TR]
[TD]PROCCOADJAMT3[/TD]
[/TR]
[TR]
[TD]PROCPROTHERAM3[/TD]
[/TR]
[TR]
[TD]PROCCOPAYAMT3[/TD]
[/TR]
[TR]
[TD]SERVICEDATEFROM3[/TD]
[/TR]
[TR]
[TD]SERVICEDATETO3[/TD]
[/TR]
[TR]
[TD]PROCPRIORAUTHNUMBER3[/TD]
[/TR]
[TR]
[TD]PROCCTLNUMBER3[/TD]
[/TR]
[TR]
[TD]PROCREMARKCODE3[/TD]
[/TR]
[TR]
[TD]PROCADJGRPCODE3[/TD]
[/TR]
[TR]
[TD]PROCADJCODE3[/TD]
[/TR]
[TR]
[TD]PROCADJAMT3[/TD]
[/TR]
[TR]
[TD]PROCADJQTY3[/TD]
[/TR]
[TR]
[TD]DATEOFSERVICE4[/TD]
[/TR]
[TR]
[TD]PROCEDURE4[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODA4[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODB4[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODC4[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODD4[/TD]
[/TR]
[TR]
[TD]PROCBILLEDAMT4[/TD]
[/TR]
[TR]
[TD]PROCPAYAMT4[/TD]
[/TR]
[TR]
[TD]PROCREVENUECODE4[/TD]
[/TR]
[TR]
[TD]PROCUNITSPAID4[/TD]
[/TR]
[TR]
[TD]PROCUNITS4[/TD]
[/TR]
[TR]
[TD]PROCEDUREDESCRIPTION4[/TD]
[/TR]
[TR]
[TD]PROCALLOWEDAMT4[/TD]
[/TR]
[TR]
[TD]PROCPERDAYLIMITAMT4[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTIONAMT4[/TD]
[/TR]
[TR]
[TD]PROCNETBILLEDAMT4[/TD]
[/TR]
[TR]
[TD]PROCTAXAMT4[/TD]
[/TR]
[TR]
[TD]PROCTOTALBEFORETAXESAMT4[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTAMT4[/TD]
[/TR]
[TR]
[TD]PROCCOINSAMT4[/TD]
[/TR]
[TR]
[TD]PROCCOADJAMT4[/TD]
[/TR]
[TR]
[TD]PROCPROTHERAM4[/TD]
[/TR]
[TR]
[TD]PROCCOPAYAMT4[/TD]
[/TR]
[TR]
[TD]SERVICEDATEFROM4[/TD]
[/TR]
[TR]
[TD]SERVICEDATETO4[/TD]
[/TR]
[TR]
[TD]PROCPRIORAUTHNUMBER4[/TD]
[/TR]
[TR]
[TD]PROCCTLNUMBER4[/TD]
[/TR]
[TR]
[TD]PROCREMARKCODE4[/TD]
[/TR]
[TR]
[TD]PROCADJGRPCODE4[/TD]
[/TR]
[TR]
[TD]PROCADJCODE4[/TD]
[/TR]
[TR]
[TD]PROCADJAMT4[/TD]
[/TR]
[TR]
[TD]PROCADJQTY4[/TD]
[/TR]
[TR]
[TD]DATEOFSERVICE5[/TD]
[/TR]
[TR]
[TD]PROCEDURE5[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODA5[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODB5[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODC5[/TD]
[/TR]
[TR]
[TD]PROCEDUREMODD5[/TD]
[/TR]
[TR]
[TD]PROCBILLEDAMT5[/TD]
[/TR]
[TR]
[TD]PROCPAYAMT5[/TD]
[/TR]
[TR]
[TD]PROCREVENUECODE5[/TD]
[/TR]
[TR]
[TD]PROCUNITSPAID5[/TD]
[/TR]
[TR]
[TD]PROCUNITS5[/TD]
[/TR]
[TR]
[TD]PROCEDUREDESCRIPTION5[/TD]
[/TR]
[TR]
[TD]PROCALLOWEDAMT5[/TD]
[/TR]
[TR]
[TD]PROCPERDAYLIMITAMT5[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTIONAMT5[/TD]
[/TR]
[TR]
[TD]PROCNETBILLEDAMT5[/TD]
[/TR]
[TR]
[TD]PROCTAXAMT5[/TD]
[/TR]
[TR]
[TD]PROCTOTALBEFORETAXESAMT5[/TD]
[/TR]
[TR]
[TD]PROCDEDUCTAMT5[/TD]
[/TR]
[TR]
[TD]PROCCOINSAMT5[/TD]
[/TR]
[TR]
[TD]PROCCOADJAMT5[/TD]
[/TR]
[TR]
[TD]PROCPROTHERAM5[/TD]
[/TR]
[TR]
[TD]PROCCOPAYAMT5[/TD]
[/TR]
[TR]
[TD]SERVICEDATEFROM5[/TD]
[/TR]
[TR]
[TD]SERVICEDATETO5[/TD]
[/TR]
[TR]
[TD]PROCPRIORAUTHNUMBER5[/TD]
[/TR]
[TR]
[TD]PROCCTLNUMBER5[/TD]
[/TR]
[TR]
[TD]PROCREMARKCODE5[/TD]
[/TR]
[TR]
[TD]PROCADJGRPCODE5[/TD]
[/TR]
[TR]
[TD]PROCADJCODE5[/TD]
[/TR]
[TR]
[TD]PROCADJAMT5[/TD]
[/TR]
[TR]
[TD]PROCADJQTY5[/TD]
[/TR]
[TR]
[TD]DATEOFSERVICE6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

There are only 17 columns in the txt file you posted, if * is the delimiter.

 

[krishnaoptif]

No it has 252 columns if we understand about ERA...

 

[Andrew Poulsom]

What's there to understand about ERA? I've never heard of it.

 

[krishnaoptif]

Please follow:https://www.unitedhealthcareonline.com/b2c/CmaAction.do?channelId=8e24829bca0ae210VgnVCM2000003010b10a____

2- https://www.humana.com/provider/support/hipaa/era

 

[Andrew Poulsom]

Those links show Application Error.