Workbook_Open subroutine RUNS TWICE

[shariryaniv]

Hello all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
i wrote some code that runs in the "Workbook_Open" subroutine.<o></o>
My problem is that the "Workbook_Open" subroutine RUNS TWICE<o></o>
When i open the excel file.<o></o>
<o></o>
IT'S IMPORTANT that i emphasize that i open the file from the command line with additional parameters.... <o></o>
For Example:<o></o>
"c:\program files\Microsoft Office\OFFICE11\excel.exe" "C:\1.xls" /e/f.Name/L.Name/Street/city name/Phone Num 1/Phone Num 2<o></o>
<o></o>
Why does the "Workbook_Open" subroutine runs twice? Can it be stoped?<o></o>
<o></o>
Thanks.

 

[hiker95]

shariryaniv,

Welcome to the MrExcel forum.

Please post your "Workbook_Open" code.

At the beginning of your posted code, enter the following without the quote marks:
["code"]


'Your code goes here.


At the end of your posted code, enter the following without the quote marks:
["/code"]

 

[shariryaniv]

hi,

at your request here is my code...

Private Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Private Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
 
Private Sub Workbook_Open()
Dim CmdRaw As Long
Dim CmdLine As String
Dim FirstPos, StrLength, ArrCels As Integer
Dim Temp As String
 
  ArrCels = 1
  CmdRaw = GetCommandLine
  CmdLine = CmdToSTr(CmdRaw)
  If InStr(1, CmdLine, "/e", vbTextCompare) Then
    FirstPos = (InStr(1, CmdLine, "/e", vbTextCompare)) + 2
    CmdLine = Right(CmdLine, Len(CmdLine) - FirstPos)
    Do
      DoEvents
      FirstPos = InStr(1, CmdLine, "/", vbTextCompare)
      If FirstPos Then
        Temp = Left(CmdLine, (FirstPos - 1))
        CmdLine = Right(CmdLine, (Len(CmdLine) - FirstPos))
        FirstPos = 0
      Else
        'ReDim Preserve ArgsArr(ArrCels)
        Worksheets("S1").Cells(ArrCels, 1).Value2 = CmdLine
        CmdLine = ""
        Exit Do
      End If
      Worksheets("S1").Cells(ArrCels, 1).Value2 = Temp
      Temp = ""
      ArrCels = ArrCels + 1
    Loop
  Else
    Exit Sub
    DoEvents
  End If
End Sub
 
Private Function CmdToSTr(Cmd As Long) As String
Dim Buffer() As Byte
Dim StrLen As Long
 
   If Cmd Then
      StrLen = lstrlenW(Cmd) * 2
      If StrLen Then
         ReDim Buffer(0 To (StrLen - 1)) As Byte
         CopyMemory Buffer(0), ByVal Cmd, StrLen
         CmdToSTr = Buffer
      End If
   End If
End Function

 

[hiker95]

shariryaniv,

Does the macro run twice if you open the workbook manually after starting Excel?

I do not have a clue.

Click on the Post Reply button and just enter the word BUMP, then click on the Submit Reply button, and someone else will assist you.

 

[xenou]

Can you explain what the Open code does and what the result is from running it?

 

[Demosthenes&Locke]

I can see what his open code does, it takes the command line and sorts cells based on the parameters, and increments a counter, and then pastes a copy of the command line into a cell on the worksheet based on that counter. I'm guessing if he didn't have it paste the command line into a cell he wouldn't even know it's running twice.

My question is, why are you using a Do loop instead of a For loop or a While loop?

I'm guessing that's where your problem is. You've got a loop without any conditions and it may be repeating because it doesn't break until the "Else" clause of the if within the loop.

Also, where are you putting it? In the workbook code section? In a module? I'm not sure if that may have an impact.

Try making the loop more than just a Do loop and make it a for or while or until loop, and break it that way so it doesn't force it to loop twice until the else breaks it, like within the if statement, keep subtracting from the parameters and deleting them from the string until the string is empty and have it say if the string is empty set a boolean to true and exit the loop on true or something.

You may want to check that it's actually doing the math right on deleting the arguments from the command variable, it may be leaving a / in there and that may cause it to execute the loop twice.

 

[shariryaniv]

hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
thank you for the well explained replay
<o></o>
the purpose of this code is to check the ability to communicate with an existing excel program... (while lunching it) by sending it some parameters.
for now the code is in the workbook code section... i'll try playing with that to.

<o></o>
i'll look closely in to your suggestions and see if it hepls.

 

[shariryaniv]

thank you all,

I fixed the problem... actually i changed a cople of small
things in the code and the problem is gone now.

the code is:

Private Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long
Private Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long)
Dim OpenSubRun As Boolean
 
 
Private Sub Workbook_Open()
Dim CmdRaw As Long
Dim CmdLine As String
Dim FirstPos, StrLength, ArrCels As Integer
Dim Temp As String
If Worksheets("S1").Cells(1, 1).Value2 <> "" Then Exit Sub
  ArrCels = 1
  CmdRaw = GetCommandLine
  CmdLine = CmdToSTr(CmdRaw)
  If InStr(1, CmdLine, "/e", vbTextCompare) Then
    FirstPos = (InStr(1, CmdLine, "/e", vbTextCompare)) + 2
    CmdLine = Right(CmdLine, Len(CmdLine) - FirstPos)
 
    Do
      DoEvents
      FirstPos = InStr(1, CmdLine, "/", vbTextCompare)
      If FirstPos > 0 Then
        Temp = Left(CmdLine, (FirstPos - 1))
        CmdLine = Right(CmdLine, (Len(CmdLine) - FirstPos))
        FirstPos = 0
      Else
        Worksheets("S1").Cells(ArrCels, 1).Value2 = CmdLine
        CmdLine = ""
        Temp = ""
        Exit Do
      End If
      Worksheets("S1").Cells(ArrCels, 1).Value2 = Temp
      Temp = ""
      ArrCels = ArrCels + 1
    Loop
  Else
    Exit Sub
  End If
End Sub
 
 
Private Function CmdToSTr(Cmd As Long) As String
Dim Buffer() As Byte
Dim StrLen As Long
 
   If Cmd Then
      StrLen = lstrlenW(Cmd) * 2
      If StrLen Then
         ReDim Buffer(0 To (StrLen - 1)) As Byte
         CopyMemory Buffer(0), ByVal Cmd, StrLen
         CmdToSTr = Buffer
      End If
   End If
End Function

Strange isn't it?....