speed up merging Merging worksheets and using find/replace
The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicodeUnicode/character issues. I cant help but think that could be handled better.
I'd like to find out where the bottlenecks in this code are, how to handle these unicodeUnicode issues, perform the Find/replace better, and all in all how to execute better vbaVBA practices.
speed up merging worksheets and using find/replace
The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues. I cant help but think that could be handled better.
I'd like to find out where the bottlenecks in this code are, how to handle these unicode issues, perform the Find/replace better, and all in all how to execute better vba practices.
Merging worksheets and using find/replace
The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of Unicode/character issues. I cant help but think that could be handled better.
I'd like to find out where the bottlenecks in this code are, how to handle these Unicode issues, perform the Find/replace better, and all in all how to execute better VBA practices.
Of all the macros that iI put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'dI'd like to know why code 16x as long is running much more instantly.
The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues - which i. I cant help but think that could be handled better.
I'd like to find out where the bottlenecks in this code are, how to handle these unicode issues, perform the Find/replace better, and all in all how to execute better vba practices.
I'd like to find out were the bottlenecks in this code are, how to handle these unicode issues or even Find/replace better, and in all how to execute better vba practices.
Of all the macros that i put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'd like to know why code 16x as long is running much more instantly.
The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues - which i cant help but think could be handled better.
I'd like to find out were the bottlenecks in this code are, how to handle these unicode issues or even Find/replace better, and in all how to execute better vba practices.
Of all the macros that I put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but I'd like to know why code 16x as long is running much more instantly.
The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues. I cant help but think that could be handled better.
I'd like to find out where the bottlenecks in this code are, how to handle these unicode issues, perform the Find/replace better, and all in all how to execute better vba practices.
speed up merging worksheets and using find/replace
Of all the macros that i put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'd like to know why code 16x as long is running much more instantly.
The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues - which i cant help but think could be handled better.
Option Explicit
Sub MergeBooks()
Dim wb As Workbook
Dim ws As Worksheet
On Error GoTo Handler:
Application.ScreenUpdating = False
For Each wb In Application.Workbooks
If wb.Name <> "CompanyBook.xlsm" Then
If FindString(wb.Name, "Report2") Then
wb.Worksheets.Move after:=Workbooks("CompanyBook.xlsm").Sheets("Aggregate")
ElseIf FindString(wb.Name, "Report1") Then
wb.Worksheets.Move after:=Workbooks("CompanyBook.xlsm").Sheets("Aggregate")
End If
End If
Next
For Each ws In Workbooks("CompanyBook.xlsm").Worksheets
If FindString(ws.Name, "Report2") Then
ws.Name = "Report2"
ElseIf FindString(ws.Name, "Report1") Then
ws.Name = "Report1"
End If
Next ws
'Char mishap replacements
With Workbooks("CompanyBook.xlsm")
.Worksheets("Report1").Cells.Replace What:="&", Replacement:="&", LookAt:=xlPart, MatchCase:=False
.Worksheets("Report1").Cells.Replace What:=""", Replacement:=Chr(34), LookAt:=xlPart, MatchCase:=False
.Worksheets("Report2").Cells.Replace What:="â€TM", Replacement:="’", LookAt:=xlPart, MatchCase:=False
.Worksheets("Report2").Cells.Replace What:="…", Replacement:="...", LookAt:=xlPart, MatchCase:=False
.Worksheets("Report2").Cells.Replace What:="£", Replacement:="£", LookAt:=xlPart, MatchCase:=False
'.Worksheets("Report2").Cells.Replace What:="†̃", Replacement:="‘L", LookAt:=xlPart, MatchCase:=False
.Worksheets("Company").Select
End With
Application.ScreenUpdating = True
Exit Sub
Handler:
Application.ScreenUpdating = True
MsgBox "Please make sure that one and only one type of each database file is open.", vbExclamation, "Merge Documents"
End Sub
Function FindString(strCheck As String, strFind As String) As Boolean
Dim intPos As Integer
intPos = InStr(strCheck, strFind)
FindString = intPos > 0
End Function
I'd like to find out were the bottlenecks in this code are, how to handle these unicode issues or even Find/replace better, and in all how to execute better vba practices.