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?
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?
