Suppress workbook display
 

I am writing some VBE code which will take some data from a workbook,
create another worknook and paste the data into this new workbook. I
have about 100 workbooks to go through.
For this code to run through all these workbooks, it takes a
considerable amount of time. Also, the current workbook, whichever it
is, keeps popping up and closing etc. I think this is a big loss
performance wise and also irritating.
Is there some way using which I don't have to Open up the workbooks on
screen and just work with them and save them in memory?

Thanks,
Harsh.

Suppress workbook display
 

Also, is there some way to stop the workbooks from changing tabs
visually on screen. The thing is, I don't need to see that stuff
happen. It should just work in memory.
I am kind of new at this stuff. So, any help would be much appreciated.
Harsh.

Suppress workbook display
 

Application.ScreenUpdating = False
' code the would update the screen
Application.ScreenUpdating = True

--
Regards,
Tom Ogilvy


"aerotops" wrote:

I am writing some VBE code which will take some data from a workbook,
create another worknook and paste the data into this new workbook. I
have about 100 workbooks to go through.
For this code to run through all these workbooks, it takes a
considerable amount of time. Also, the current workbook, whichever it
is, keeps popping up and closing etc. I think this is a big loss
performance wise and also irritating.
Is there some way using which I don't have to Open up the workbooks on
screen and just work with them and save them in memory?

Thanks,
Harsh.

Suppress workbook display
 

Thanks a lot.
I also want to strip all special characters from filenames. Is there
any quick fix for that? If not, how should I go about it?
Harsh.

Suppress workbook display
 

Never mind. I found some code online which does this. I am pasting it
here for future reference. The link is at:
http://www.vba-programmer.com/Snippe..._Function.html

Private Function DeleteWithin(ByVal TheString As String, ByVal BadTerm
As String)
START_PROCESS:
If InStr(TheString, BadTerm) 0 Then
OldLength = Len(BadTerm)
TotalLength = Len(TheString)
InnerStringPos = InStr(TheString, BadTerm)
LeftSide = Left(TheString, InnerStringPos - 1)
RightSide = Right(TheString, TotalLength - (InnerStringPos +
OldLength - 1))
TheString = LeftSide & RightSide
GoTo START_PROCESS
Else
DeleteWithin = TheString
End If
End Function

Suppress workbook display
 

Perhaps something along the lines of:

Dim s as String, s1 as String, i as Long
Dim sChr as String, sChr1 as String
Dim bk as Workbook
set bk = Workbooks("???")
s = bk.Filename
s1 = ""
for i = 1 to len(s)
sChr = Mid(s,i,1)
sChr1 = Ucase(sChr)
if sChr < sChr1 or sChr = "." or sChr = " " then
s1 = s1 & sChr
end if
Next
bk.SaveAs bk.Path & "\" & s1

--
regards,
Tom Ogilvy


"aerotops" wrote:

Thanks a lot.
I also want to strip all special characters from filenames. Is there
any quick fix for that? If not, how should I go about it?
Harsh.

Suppress workbook display
 

Looping with a Goto; haven't seen that approach posted in years. Thanks for
the flashback.

--
Regards,
Tom Ogilvy


"aerotops" wrote:

Never mind. I found some code online which does this. I am pasting it
here for future reference. The link is at:
http://www.vba-programmer.com/Snippe..._Function.html

Private Function DeleteWithin(ByVal TheString As String, ByVal BadTerm
As String)
START_PROCESS:
If InStr(TheString, BadTerm) 0 Then
OldLength = Len(BadTerm)
TotalLength = Len(TheString)
InnerStringPos = InStr(TheString, BadTerm)
LeftSide = Left(TheString, InnerStringPos - 1)
RightSide = Right(TheString, TotalLength - (InnerStringPos +
OldLength - 1))
TheString = LeftSide & RightSide
GoTo START_PROCESS
Else
DeleteWithin = TheString
End If
End Function