MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Code getting ahead of itself


Posted by marlon on July 24, 2001 5:23 AM

I have written an excel macro that edits the macro on an existing file. And Loops to go to the next excel file in the same folder. The problem is that it seems to be opening the other workbooks before its finsihed with the first one. This causes the wrong macro to be edited. I need a code that will tell the macro to hold on until the first workbook is saved and closed. I've already tried a text message, using arrays, splitting the procedure to more than one procedure. None of these have worked. Can anyone help me, please???


Posted by Sofia on July 24, 2001 6:06 AM

have you tried using:

call function1
MsgBox "Function 1 complete"
call function2
MsgBox "Function 2 complete"

That works for me in theese instances.

Posted by Jerid on July 24, 2001 7:07 AM

Hi Marlon, can you provide a sample of your code.

Jerid

Posted by marlon on July 24, 2001 7:17 AM


Sub Link_budget_Changes()
'

'Macro written by Marlon 2/28/01
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim MyFile, MyFile1 As String
Dim MyArray(50) As Variant
Dim X, y As Integer

X = 0

MyFile = Dir$("C:\WINDOWS\DESKTOP\MACRO\*.xls")

'creates string of directory and filename
MyFile = "c:\windows\desktop\macro\" & MyFile
'This loop creates an array of all the file names in the folder
Do
MyFile = Dir$
MyFile = "c:\windows\desktop\macro\" & MyFile

MyArray(X) = MyFile

X = X + 1

Loop Until MyArray(X - 1) = "c:\windows\desktop\macro\"

y = X - 1

MsgBox "There are " & y & " files"

X = 0

Do Until X = y

MsgBox "Function " & X & " complete"

Workbooks.Open FileName:=MyArray(X)

Keys_send

SendKeys "^{F4}"

MsgBox "hold on"

X = X + 1


Loop
End Sub

Sub Keys_send()


SendKeys "%{F8}"

SendKeys "{TAB}"

SendKeys "{DOWN 6}"

SendKeys "{TAB 2}"

SendKeys "{DOWN 3}"

SendKeys "{ENTER}"

SendKeys "^h"

SendKeys "c:\Link Budget"

SendKeys "{TAB}"

SendKeys "T:\Link Budget\CentralRegion"

SendKeys "{TAB 6}"

SendKeys "{DOWN 3}"

SendKeys "{ENTER 2}"

SendKeys "{UP 2}"

SendKeys "{ENTER}"

SendKeys "^s"

SendKeys "%q"

End Sub

Sub Message()
MsgBox "Take a break"

End Sub


Posted by Jerid on July 24, 2001 1:14 PM

Sub Link_budget_Changes() ' Dim Msg, Style, Title, Help, Ctxt, Response, MyString Dim MyFile, MyFile1 As String Dim MyArray(50) As Variant Dim X, y As Integer MyFile = "c:\windows\desktop\macro\" & MyFile 'This loop creates an array of all the file names in the folder Do MyFile = Dir$ MyFile = "c:\windows\desktop\macro\" & MyFile Loop End Sub Sub Keys_send() SendKeys "%{F8}" SendKeys "{TAB}" SendKeys "{DOWN 6}" SendKeys "{TAB 2}" SendKeys "{DOWN 3}" SendKeys "{ENTER}" SendKeys "^h" SendKeys "c:\Link Budget" SendKeys "{TAB}" SendKeys "T:\Link Budget\CentralRegion" SendKeys "{TAB 6}" SendKeys "{DOWN 3}" SendKeys "{ENTER 2}" SendKeys "{UP 2}" SendKeys "{ENTER}" SendKeys "^s" SendKeys "%q" End Sub Sub Message() MsgBox "Take a break" End Sub

Marlon, your problem is the SendKeys, try this code.

Sub Link_budget_Changes()

Dim MyFile As String, sFindString As String, sReplaceString As String
Dim sPath As String, sBook2Change As String
Dim MyArray() As String
Dim iX As Integer

'Assign variables
iX = 0
sPath = "C:\WINDOWS\DESKTOP\MACRO\"
sFindString = "c:\Link Budget"
sReplaceString = "T:\Link Budget\CentralRegion"

'Get first file
MyFile = sPath & Dir$(sPath & "*.xls")

Do
'This method will make your array as large as you need it, instead of
'dimensioning 50 elements up front, saves on memory.
ReDim Preserve MyArray(iX)

'Add the current value of MyFile to the array
MyArray(iX) = MyFile

'Get next file
MyFile = sPath & Dir$

'increment counter
iX = iX + 1

'Stop if Dir$ returns just the directory name (No more files)
Loop Until MyFile = sPath

MsgBox "There are " & iX & " files"

iX = 0
For iX = 0 To UBound(MyArray)
Workbooks.Open FileName:=MyArray(iX)
sBook2Change = ActiveWorkbook.Name
Call ReplaceCode(sBook2Change, sFindString, sReplaceString)
Workbooks(sBook2Change).Close True
MsgBox "File " & iX + 1 & " complete"
Next iX

End Sub

Sub ReplaceCode(sBook2Change As String, sFindString As String, sReplaceString As String)
'This procedure looks at each line of code in your project and if it
'find the string sFindString it replaces it with the string sReplaceString

Dim iNumLinesCode As Variant
Dim iX As Integer, iY As Integer, iFound As Integer
Dim sOrgText As String, sNewLine As String

With Workbooks(sBook2Change).VBProject.VBComponents
For iX = 1 To .Count
For iY = 1 To .Item(iX).CodeModule.CountOfLines
sOrgText = .Item(iX).CodeModule.Lines(iY, 1)
iFound = InStr(1, sOrgText, sFindString, vbTextCompare)
If iFound > 0 Then
sNewLine = Mid(sOrgText, 1, iFound - 1)
sNewLine = sNewLine & sReplaceString
sNewLine = sNewLine & Mid(sOrgText, iFound + Len(sFindString), Len(sOrgText))
.Item(iX).CodeModule.ReplaceLine iY, sNewLine
End If
Next iY
Next iX
End With
End Sub

Jerid

Posted by marlon on July 25, 2001 9:07 AM