billgilmer
New Member
- Joined
- Dec 30, 2004
- Messages
- 11
I have a macro / macros that that I need to run every week.
I would like to have a menu to select the file and then run the macro to format the text file.
: This is only a part of the macro I edit each week:
You can see that it repeats itself, only changing the "week and folder" their are over 5 folders per month and a 170 sub folders under each week.
Hope you can help, Thanks!!
billgilmer@satx.rr.com
Sub BG()
'
' BG Macro
' Macro recorded 12/1/2005 by Bill
'
' Keyboard Shortcut: Ctrl+k
'
Sheets("47").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;I:\CPYDPTH\05stores\Oct_5\47\totals.txt", Destination:=Range("A1"))
.Name = "totals"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(31, 9, 16, 12, 17, 8, 8, 8)
.Refresh BackgroundQuery:=False
End With
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 1
Columns("A:A").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("E1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveSheet.Paste
Range("I1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("M1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("O1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Columns("O:O").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 1
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=1
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Sheets("932").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;I:\CPYDPTH\05stores\Oct_5\932\totals.txt", Destination:=Range("A1"))
.Name = "totals"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(31, 9, 16, 12, 17, 8, 8, 8)
.Refresh BackgroundQuery:=False
End With
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 1
Columns("A:A").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("E1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveSheet.Paste
Range("I1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("M1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("O1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Columns("O:O").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 1
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=1
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Sheets("933").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;I:\CPYDPTH\05stores\Oct_5\933\totals.txt", Destination:=Range("A1"))
.Name = "totals"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(31, 9, 16, 12, 17, 8, 8, 8)
.Refresh BackgroundQuery:=False
End With
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 1
Columns("A:A").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("E1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveSheet.Paste
Range("I1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("M1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("O1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Columns("O:O").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 1
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=1
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
I would like to have a menu to select the file and then run the macro to format the text file.
: This is only a part of the macro I edit each week:
You can see that it repeats itself, only changing the "week and folder" their are over 5 folders per month and a 170 sub folders under each week.
Hope you can help, Thanks!!
billgilmer@satx.rr.com
Sub BG()
'
' BG Macro
' Macro recorded 12/1/2005 by Bill
'
' Keyboard Shortcut: Ctrl+k
'
Sheets("47").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;I:\CPYDPTH\05stores\Oct_5\47\totals.txt", Destination:=Range("A1"))
.Name = "totals"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(31, 9, 16, 12, 17, 8, 8, 8)
.Refresh BackgroundQuery:=False
End With
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 1
Columns("A:A").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("E1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveSheet.Paste
Range("I1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("M1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("O1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Columns("O:O").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 1
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=1
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Sheets("932").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;I:\CPYDPTH\05stores\Oct_5\932\totals.txt", Destination:=Range("A1"))
.Name = "totals"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(31, 9, 16, 12, 17, 8, 8, 8)
.Refresh BackgroundQuery:=False
End With
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 1
Columns("A:A").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("E1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveSheet.Paste
Range("I1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("M1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("O1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Columns("O:O").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 1
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=1
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Sheets("933").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;I:\CPYDPTH\05stores\Oct_5\933\totals.txt", Destination:=Range("A1"))
.Name = "totals"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(31, 9, 16, 12, 17, 8, 8, 8)
.Refresh BackgroundQuery:=False
End With
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Columns("K:K").Select
Selection.Insert Shift:=xlToRight
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=1
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.ScrollColumn = 1
Columns("A:A").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("E1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Range("G1").Select
ActiveSheet.Paste
Range("I1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=3
Range("M1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=1
Range("O1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=4
Columns("O:O").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 1
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=1
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.SmallScroll ToRight:=2
Columns("I:I").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 1