Workbook that references two text files that update daily.

[silverback24]

I am having to set up a workbook for our purchasing department that compares two daily exports from our database and tells her what to order to maintain appropriate levels of inventory. I can make all the formulas and know how to get this to work if the two exports were excel files. Can the same be done if the exports are text format? Typically we copy the export into excel and text to column with default settings before we work with them.

I don't have the exports yet to give an example, so this is just a can it be done question.

Thanks for any help.

 

[VBA Geek]

You can have a code which imports these text files for you on your spreadsheet

 

[silverback24]

So this would update the tabs when the files update? Thanks for your fast response.

 

[VBA Geek]

you could read the content of each of the text files and store them into two arrays and compare them
or print the contents of both arrays on a separate sheet and have some formulas that look up the information that has changed and update your reports

 

[silverback24]

Here is the code I have recorded. This works manually, but when I try and run the macro it gives me an error with ".CommandType = 0". Any ideas? Am I going down the correct path?

Sub importfiles()
'
' importfiles Macro
'

'
'Clear sheets
Sheets("CALFG").Select
Cells.Select
Selection.ClearContents
Sheets("ZZZ").Select
Cells.Select
Selection.ClearContents
Sheets("Daily Results").Select

'Import Files and Format
Sheets("CALFG").Select
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\txt\CALFG.TXT", _
Destination:=Range("$A$2"))
.CommandType = 0
.Name = "CALFG"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("A1").Select
ActiveCell.FormulaR1C1 = "P/N"
Range("B1").Select
ActiveCell.FormulaR1C1 = "QH1"
Range("C1").Select
ActiveCell.FormulaR1C1 = "QO1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "CD2"
Range("E1").Select
ActiveCell.FormulaR1C1 = "11"
Range("F1").Select
ActiveCell.FormulaR1C1 = "21"
Range("G1").Select
ActiveCell.FormulaR1C1 = "31"
Range("H1").Select
ActiveCell.FormulaR1C1 = "41"
Range("I1").Select
ActiveCell.FormulaR1C1 = "51"
Range("J1").Select
ActiveCell.FormulaR1C1 = "61"
Range("K1").Select
ActiveCell.FormulaR1C1 = "71"
Range("L1").Select
ActiveCell.FormulaR1C1 = "81"
Range("M1").Select
ActiveCell.FormulaR1C1 = "91"
Range("N1").Select
ActiveCell.FormulaR1C1 = "101"
Range("O1").Select
ActiveCell.FormulaR1C1 = "111"
Range("P1").Select
ActiveCell.FormulaR1C1 = "121"
Columns("A:P").Select
Columns("A:P").EntireColumn.AutoFit
Sheets("ZZZ").Select
Range("A2").Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\txt\ZZZ.TXT", _
Destination:=Range("$A$2"))
.CommandType = 0
.Name = "ZZZ"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("A1").Select
ActiveCell.FormulaR1C1 = "P/N"
Range("B1").Select
ActiveCell.FormulaR1C1 = "QH7"
Range("C1").Select
ActiveCell.FormulaR1C1 = "QO7"
Range("D1").Select
ActiveCell.FormulaR1C1 = "CS1"
Range("A1:D1").Select
Columns("D:D").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
End Sub