Below is an existing macro that automatically imports data from a text file onto a new sheet and then runs a sub to format and then repeats for a second sheet before ending the sub.
One feature I would like to add to this is a way to automatically name the 2 different sheets it creates based on the following:
First Sheet = "Client 1" & Today's Date in format (Month and Day)
Second Sheet = "Associate" & Today's Date in format (Month and Day)
Also I have concern about certain days I have to run this more than once which may add a duplicate name. So if there is a way of handling this
Any help would be greatly appreciated.
Thank you.
Sub Add_Sheets()
'
' addsheets Macro
'
'
Sheets.Add After:=ActiveSheet
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\production\Desktop\Orders\C-1.txt", _
Destination:=Range("$A$1"))
'.CommandType = 0
.Name = "1 with smi"
.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, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Application.Run "'Inventorysys.xlsm'!Order_Format"
Sheets.Add After:=ActiveSheet
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\production\Desktop\Orders\Associate_Area.txt", _
:=Range("$A$1"))
'.CommandType = 0
.Name = "2.25"
.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, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Application.Run "'Inventorysys.xlsm'!Order_Format"
End Sub
One feature I would like to add to this is a way to automatically name the 2 different sheets it creates based on the following:
First Sheet = "Client 1" & Today's Date in format (Month and Day)
Second Sheet = "Associate" & Today's Date in format (Month and Day)
Also I have concern about certain days I have to run this more than once which may add a duplicate name. So if there is a way of handling this
Any help would be greatly appreciated.
Thank you.
Sub Add_Sheets()
'
' addsheets Macro
'
'
Sheets.Add After:=ActiveSheet
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\production\Desktop\Orders\C-1.txt", _
Destination:=Range("$A$1"))
'.CommandType = 0
.Name = "1 with smi"
.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, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Application.Run "'Inventorysys.xlsm'!Order_Format"
Sheets.Add After:=ActiveSheet
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\production\Desktop\Orders\Associate_Area.txt", _
:=Range("$A$1"))
'.CommandType = 0
.Name = "2.25"
.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, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Application.Run "'Inventorysys.xlsm'!Order_Format"
End Sub