cav~firez22
Well-known Member
- Joined
- Jun 21, 2006
- Messages
- 543
Hello everyone,
I have a rather LONG macro, that opens 11 files and copies the data into 1 workbook.
the 11 files are always in the same location, and named the same each day i run this. MY question is, if the file the macro is trying to open, is not there, how can i make it just skip it instead of a debug error.
Here is the intensly long code.
If anyone could help with this that would be great. Thanks Much
I have a rather LONG macro, that opens 11 files and copies the data into 1 workbook.
the 11 files are always in the same location, and named the same each day i run this. MY question is, if the file the macro is trying to open, is not there, how can i make it just skip it instead of a debug error.
Here is the intensly long code.
Code:
Sub opencashdepos()
'1 ATLANTA
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\ATL" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("ATL").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("atl").Close
'2 JACKSON
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\JCK" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("JCK").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("JCK").Close
'3 SLC
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\SLC" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("SLC").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("SLC").Close
'4 SAC
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\sac" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("sac").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("sac").Close
'5 fif
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\fif" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("fif").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("fif").Close
'6 ver
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\ver" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("ver").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("ver").Close
'7 edm
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\edm" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("edm").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("edm").Close
'8 van
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\van" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("van").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("van").Close
'9 tor
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\tor" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("tor").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("tor").Close
'10 hfx
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\hfx" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("hfx").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("hfx").Close
'11 que
Application.ScreenUpdating = False
ChDir "O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data"
Workbooks.OpenText Filename:= _
"O:\Accounts Receivable\AR Reports\Cash Deposits\Cash Deposit Data\que" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 3), Array(11, 1), Array(19, 1), Array(40, 1), Array(52, 3), _
Array(62, 1), Array(71, 1)), TrailingMinusNumbers:=True
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("F1:F106"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A:G")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Rows(Columns("a").Find("-*", , , xlWhole).Row & ":" & Rows.Count).Delete
Range("A1:F1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("BLANK ALL AGENCIES Worksheet.xls").Activate
Sheets("que").Select
Range("A2").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Windows("que").Close
Application.ScreenUpdating = True
'Call SAVEAScd
'msgbox (Be sure to saveAS ALL AGENCIES Worksheet)
End Sub
If anyone could help with this that would be great. Thanks Much