Select and Import delimited data file (ERA)

Troutwater

New Member
Joined
May 1, 2008
Messages
17
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?
 
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]
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top