Open/Close several workbooks to sort data
up vote
1
down vote
favorite
I have written a macro to process all excel files within a user-chosen folder and then save the processed files as a new file in a new folder ("FINAL"). The macro I have works, but is slow. Do you have any suggestions as to how I can improve the speed?
Questions:
Can I process these files without actually opening and closing the excel file? Is the opening and closing of the file slowing down the process?
Is there a better way to code the sort process? For Worksheet("Notes") there is data in all rows of Column "A", and in Worksheet("Orders"), Column "A" contains empty row gaps (3-5 empty rows between rows with data).
Sub PreProcessing()
Application.Calculation = xlCalculationManual
Application.EnableAnimations = False
Application.DisplayStatusBar = False
'Choose Folder
Set FolderPath = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPath
.AllowMultiSelect = False
.Show
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ChosenFolder = FolderPath.SelectedItems(1)
GetDirectory = Mid(ChosenFolder, InStrRev(ChosenFolder, "") + 1)
ChosenFile = Dir(ChosenFolder & "*Output_Final*")
'Loop through files in the folder
Do While Len(ChosenFile) > 0
'Open The Workbook
Workbooks.Open Filename:=GetDirectory & "" & ChosenFile
'Format "Notes" Worksheet
With Cells
.ClearFormats
.RowHeight = 14.4
.ColumnWidth = 8.11
End With
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & LR).ClearContents
Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Add _
Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
'Format "Orders" Worksheet
Sheets("Orders").Select
With Cells
.ClearFormats
.RowHeight = 14.4
.ColumnWidth = 8.11
End With
LastCell = Range("A1").SpecialCells(xlCellTypeLastCell).Address
Columns("A:A").Select
ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Add _
Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Orders").Sort
.SetRange Range("A2:" & LastCell)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Delete remaining sheets
Application.DisplayAlerts = False
Sheets("C").Delete
Sheets("D").Delete
Sheets("E").Delete
'Save file
Sheets("Notes").Select
strFileFullName = ActiveWorkbook.FullName
SaveHere = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "")) & "FINAL"
NewName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & "_Processed"
newFileFullPath = SaveHere & NewName & ".xlsx"
ActiveWorkbook.SaveAs Filename:=newFileFullPath
ActiveWorkbook.Close
ChosenFile = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.EnableAnimations = True
Application.DisplayStatusBar = True
MsgBox "Pre-Processing Complete for " & GetDirectory
End Sub
EDITED: The Excel documents I am processing have some weird formatting byproducts by the software that produced the file. I just want to remove these so that the Excel files are essentially plain text files which can be used later by a different program. There are 50+ documents that I need to process - which is why I am looking to optimize a macro, so that I don't need to manually open/close/reformat each file individually.
These are the steps that I need to perform for each Excel file:
Open Excel file
In the "Notes" folder, remove all formats, re-adjust column width & row height. Delete the final cell in Column "A".
In the "Notes" folder, remove all formats, re-adjust column width & row height. Also sort on Column "A" so that there are no empty rows within the data.
Remove the rest of the sheets in the workbook ("C", "D", "E").
Save As new file. Close the original workbook without saving.
performance vba excel
add a comment |
up vote
1
down vote
favorite
I have written a macro to process all excel files within a user-chosen folder and then save the processed files as a new file in a new folder ("FINAL"). The macro I have works, but is slow. Do you have any suggestions as to how I can improve the speed?
Questions:
Can I process these files without actually opening and closing the excel file? Is the opening and closing of the file slowing down the process?
Is there a better way to code the sort process? For Worksheet("Notes") there is data in all rows of Column "A", and in Worksheet("Orders"), Column "A" contains empty row gaps (3-5 empty rows between rows with data).
Sub PreProcessing()
Application.Calculation = xlCalculationManual
Application.EnableAnimations = False
Application.DisplayStatusBar = False
'Choose Folder
Set FolderPath = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPath
.AllowMultiSelect = False
.Show
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ChosenFolder = FolderPath.SelectedItems(1)
GetDirectory = Mid(ChosenFolder, InStrRev(ChosenFolder, "") + 1)
ChosenFile = Dir(ChosenFolder & "*Output_Final*")
'Loop through files in the folder
Do While Len(ChosenFile) > 0
'Open The Workbook
Workbooks.Open Filename:=GetDirectory & "" & ChosenFile
'Format "Notes" Worksheet
With Cells
.ClearFormats
.RowHeight = 14.4
.ColumnWidth = 8.11
End With
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & LR).ClearContents
Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Add _
Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
'Format "Orders" Worksheet
Sheets("Orders").Select
With Cells
.ClearFormats
.RowHeight = 14.4
.ColumnWidth = 8.11
End With
LastCell = Range("A1").SpecialCells(xlCellTypeLastCell).Address
Columns("A:A").Select
ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Add _
Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Orders").Sort
.SetRange Range("A2:" & LastCell)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Delete remaining sheets
Application.DisplayAlerts = False
Sheets("C").Delete
Sheets("D").Delete
Sheets("E").Delete
'Save file
Sheets("Notes").Select
strFileFullName = ActiveWorkbook.FullName
SaveHere = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "")) & "FINAL"
NewName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & "_Processed"
newFileFullPath = SaveHere & NewName & ".xlsx"
ActiveWorkbook.SaveAs Filename:=newFileFullPath
ActiveWorkbook.Close
ChosenFile = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.EnableAnimations = True
Application.DisplayStatusBar = True
MsgBox "Pre-Processing Complete for " & GetDirectory
End Sub
EDITED: The Excel documents I am processing have some weird formatting byproducts by the software that produced the file. I just want to remove these so that the Excel files are essentially plain text files which can be used later by a different program. There are 50+ documents that I need to process - which is why I am looking to optimize a macro, so that I don't need to manually open/close/reformat each file individually.
These are the steps that I need to perform for each Excel file:
Open Excel file
In the "Notes" folder, remove all formats, re-adjust column width & row height. Delete the final cell in Column "A".
In the "Notes" folder, remove all formats, re-adjust column width & row height. Also sort on Column "A" so that there are no empty rows within the data.
Remove the rest of the sheets in the workbook ("C", "D", "E").
Save As new file. Close the original workbook without saving.
performance vba excel
You need to tell us what your code is doing in more details because opening/closing files in not the only thing you do.
– t3chb0t
Nov 18 at 9:02
1
@t3chb0t I have edited the question to explain in more detail what I need to do with each of the files.
– ahhn
Nov 18 at 16:13
I'm a bit confused - you say you're doing this "so that the Excel files are essentially plain text files", but you're not saving as csv or any other text format (and can't, because you're retaining 2 worksheets). What are the input requirements of the "different program"? Does it really even care about the formatting? That said, you can probably do this easily without opening the files with a couple ADODB queries with anORDER BY
clause.
– Comintern
Nov 18 at 16:57
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have written a macro to process all excel files within a user-chosen folder and then save the processed files as a new file in a new folder ("FINAL"). The macro I have works, but is slow. Do you have any suggestions as to how I can improve the speed?
Questions:
Can I process these files without actually opening and closing the excel file? Is the opening and closing of the file slowing down the process?
Is there a better way to code the sort process? For Worksheet("Notes") there is data in all rows of Column "A", and in Worksheet("Orders"), Column "A" contains empty row gaps (3-5 empty rows between rows with data).
Sub PreProcessing()
Application.Calculation = xlCalculationManual
Application.EnableAnimations = False
Application.DisplayStatusBar = False
'Choose Folder
Set FolderPath = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPath
.AllowMultiSelect = False
.Show
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ChosenFolder = FolderPath.SelectedItems(1)
GetDirectory = Mid(ChosenFolder, InStrRev(ChosenFolder, "") + 1)
ChosenFile = Dir(ChosenFolder & "*Output_Final*")
'Loop through files in the folder
Do While Len(ChosenFile) > 0
'Open The Workbook
Workbooks.Open Filename:=GetDirectory & "" & ChosenFile
'Format "Notes" Worksheet
With Cells
.ClearFormats
.RowHeight = 14.4
.ColumnWidth = 8.11
End With
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & LR).ClearContents
Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Add _
Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
'Format "Orders" Worksheet
Sheets("Orders").Select
With Cells
.ClearFormats
.RowHeight = 14.4
.ColumnWidth = 8.11
End With
LastCell = Range("A1").SpecialCells(xlCellTypeLastCell).Address
Columns("A:A").Select
ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Add _
Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Orders").Sort
.SetRange Range("A2:" & LastCell)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Delete remaining sheets
Application.DisplayAlerts = False
Sheets("C").Delete
Sheets("D").Delete
Sheets("E").Delete
'Save file
Sheets("Notes").Select
strFileFullName = ActiveWorkbook.FullName
SaveHere = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "")) & "FINAL"
NewName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & "_Processed"
newFileFullPath = SaveHere & NewName & ".xlsx"
ActiveWorkbook.SaveAs Filename:=newFileFullPath
ActiveWorkbook.Close
ChosenFile = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.EnableAnimations = True
Application.DisplayStatusBar = True
MsgBox "Pre-Processing Complete for " & GetDirectory
End Sub
EDITED: The Excel documents I am processing have some weird formatting byproducts by the software that produced the file. I just want to remove these so that the Excel files are essentially plain text files which can be used later by a different program. There are 50+ documents that I need to process - which is why I am looking to optimize a macro, so that I don't need to manually open/close/reformat each file individually.
These are the steps that I need to perform for each Excel file:
Open Excel file
In the "Notes" folder, remove all formats, re-adjust column width & row height. Delete the final cell in Column "A".
In the "Notes" folder, remove all formats, re-adjust column width & row height. Also sort on Column "A" so that there are no empty rows within the data.
Remove the rest of the sheets in the workbook ("C", "D", "E").
Save As new file. Close the original workbook without saving.
performance vba excel
I have written a macro to process all excel files within a user-chosen folder and then save the processed files as a new file in a new folder ("FINAL"). The macro I have works, but is slow. Do you have any suggestions as to how I can improve the speed?
Questions:
Can I process these files without actually opening and closing the excel file? Is the opening and closing of the file slowing down the process?
Is there a better way to code the sort process? For Worksheet("Notes") there is data in all rows of Column "A", and in Worksheet("Orders"), Column "A" contains empty row gaps (3-5 empty rows between rows with data).
Sub PreProcessing()
Application.Calculation = xlCalculationManual
Application.EnableAnimations = False
Application.DisplayStatusBar = False
'Choose Folder
Set FolderPath = Application.FileDialog(msoFileDialogFolderPicker)
With FolderPath
.AllowMultiSelect = False
.Show
End With
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ChosenFolder = FolderPath.SelectedItems(1)
GetDirectory = Mid(ChosenFolder, InStrRev(ChosenFolder, "") + 1)
ChosenFile = Dir(ChosenFolder & "*Output_Final*")
'Loop through files in the folder
Do While Len(ChosenFile) > 0
'Open The Workbook
Workbooks.Open Filename:=GetDirectory & "" & ChosenFile
'Format "Notes" Worksheet
With Cells
.ClearFormats
.RowHeight = 14.4
.ColumnWidth = 8.11
End With
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & LR).ClearContents
Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort.SortFields.Add _
Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Notes").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(Cells(1,1), Cells(1,1).End(xlToRight)).AutoFilter
'Format "Orders" Worksheet
Sheets("Orders").Select
With Cells
.ClearFormats
.RowHeight = 14.4
.ColumnWidth = 8.11
End With
LastCell = Range("A1").SpecialCells(xlCellTypeLastCell).Address
Columns("A:A").Select
ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Orders").Sort.SortFields.Add _
Key:=Range("A1"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Orders").Sort
.SetRange Range("A2:" & LastCell)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Delete remaining sheets
Application.DisplayAlerts = False
Sheets("C").Delete
Sheets("D").Delete
Sheets("E").Delete
'Save file
Sheets("Notes").Select
strFileFullName = ActiveWorkbook.FullName
SaveHere = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "")) & "FINAL"
NewName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & "_Processed"
newFileFullPath = SaveHere & NewName & ".xlsx"
ActiveWorkbook.SaveAs Filename:=newFileFullPath
ActiveWorkbook.Close
ChosenFile = Dir
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.EnableAnimations = True
Application.DisplayStatusBar = True
MsgBox "Pre-Processing Complete for " & GetDirectory
End Sub
EDITED: The Excel documents I am processing have some weird formatting byproducts by the software that produced the file. I just want to remove these so that the Excel files are essentially plain text files which can be used later by a different program. There are 50+ documents that I need to process - which is why I am looking to optimize a macro, so that I don't need to manually open/close/reformat each file individually.
These are the steps that I need to perform for each Excel file:
Open Excel file
In the "Notes" folder, remove all formats, re-adjust column width & row height. Delete the final cell in Column "A".
In the "Notes" folder, remove all formats, re-adjust column width & row height. Also sort on Column "A" so that there are no empty rows within the data.
Remove the rest of the sheets in the workbook ("C", "D", "E").
Save As new file. Close the original workbook without saving.
performance vba excel
performance vba excel
edited Nov 18 at 16:22
t3chb0t
33.7k744108
33.7k744108
asked Nov 18 at 6:04
ahhn
62
62
You need to tell us what your code is doing in more details because opening/closing files in not the only thing you do.
– t3chb0t
Nov 18 at 9:02
1
@t3chb0t I have edited the question to explain in more detail what I need to do with each of the files.
– ahhn
Nov 18 at 16:13
I'm a bit confused - you say you're doing this "so that the Excel files are essentially plain text files", but you're not saving as csv or any other text format (and can't, because you're retaining 2 worksheets). What are the input requirements of the "different program"? Does it really even care about the formatting? That said, you can probably do this easily without opening the files with a couple ADODB queries with anORDER BY
clause.
– Comintern
Nov 18 at 16:57
add a comment |
You need to tell us what your code is doing in more details because opening/closing files in not the only thing you do.
– t3chb0t
Nov 18 at 9:02
1
@t3chb0t I have edited the question to explain in more detail what I need to do with each of the files.
– ahhn
Nov 18 at 16:13
I'm a bit confused - you say you're doing this "so that the Excel files are essentially plain text files", but you're not saving as csv or any other text format (and can't, because you're retaining 2 worksheets). What are the input requirements of the "different program"? Does it really even care about the formatting? That said, you can probably do this easily without opening the files with a couple ADODB queries with anORDER BY
clause.
– Comintern
Nov 18 at 16:57
You need to tell us what your code is doing in more details because opening/closing files in not the only thing you do.
– t3chb0t
Nov 18 at 9:02
You need to tell us what your code is doing in more details because opening/closing files in not the only thing you do.
– t3chb0t
Nov 18 at 9:02
1
1
@t3chb0t I have edited the question to explain in more detail what I need to do with each of the files.
– ahhn
Nov 18 at 16:13
@t3chb0t I have edited the question to explain in more detail what I need to do with each of the files.
– ahhn
Nov 18 at 16:13
I'm a bit confused - you say you're doing this "so that the Excel files are essentially plain text files", but you're not saving as csv or any other text format (and can't, because you're retaining 2 worksheets). What are the input requirements of the "different program"? Does it really even care about the formatting? That said, you can probably do this easily without opening the files with a couple ADODB queries with an
ORDER BY
clause.– Comintern
Nov 18 at 16:57
I'm a bit confused - you say you're doing this "so that the Excel files are essentially plain text files", but you're not saving as csv or any other text format (and can't, because you're retaining 2 worksheets). What are the input requirements of the "different program"? Does it really even care about the formatting? That said, you can probably do this easily without opening the files with a couple ADODB queries with an
ORDER BY
clause.– Comintern
Nov 18 at 16:57
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f207897%2fopen-close-several-workbooks-to-sort-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You need to tell us what your code is doing in more details because opening/closing files in not the only thing you do.
– t3chb0t
Nov 18 at 9:02
1
@t3chb0t I have edited the question to explain in more detail what I need to do with each of the files.
– ahhn
Nov 18 at 16:13
I'm a bit confused - you say you're doing this "so that the Excel files are essentially plain text files", but you're not saving as csv or any other text format (and can't, because you're retaining 2 worksheets). What are the input requirements of the "different program"? Does it really even care about the formatting? That said, you can probably do this easily without opening the files with a couple ADODB queries with an
ORDER BY
clause.– Comintern
Nov 18 at 16:57