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.

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.

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.

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.

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

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.

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