PDA

View Full Version : Double run problem



vloneboy
06-05-2019, 12:56 AM
Hey guys,

I have programmed a code which transfers data from an excel sheet to a word document. In my code I first run a code which sets a reference and than imports the "transfering code". At this stage my problem is occuring: the code is running two times and therefore I get two word documents. How can I prevent that ?

Here is my code which imports my transfering code, maybe the problem is occuring at this stage:

ThisWorkbook.VBProject.VBComponents.Import ("K:\VbaCodes\X\Company\Modul17.bas")

ThisWorkbook.Application.Run ("Modul15.Export()")

I hope someone can help me, Thank you.

Artik
06-05-2019, 04:16 AM
You showed too little.
However, try the Export() procedure call through the OnTime event, not the Run method.
Please remember that after interfering the VBA project by the code (change of procedure, adding a module and others) you have to give the possibility to recompile the project. Therefore, the rest of the code to be executed must be run by OnTime.
If this does not help, prepare a short example in the attachment where the procedure will be run twice.

Artik

vloneboy
06-05-2019, 05:40 AM
Thanks for the respond Artik.

What is the code for running the on time event ?

Artik
06-05-2019, 08:05 AM
Paste into the standard module
Sub ProcedureAAA() Dim oModul As Object

'Simulation of interference in the VBA project
Call AddModule
Call AddProcedure

'version 1
'calling other procedures by OnTime
'(run the procedure in a different thread than the current one)
Application.OnTime Now, "ProcedureBBB"

'version 2
'run the procedure in the same thread
'Application.Run "ProcedureBBB"

MsgBox "End of ProcedureAAA"
End Sub




Sub AddModule()

Const vbext_ct_StdModule As Long = 1

ThisWorkbook.VBProject.VBComponents.Add vbext_ct_StdModule

End Sub




Sub AddProcedure()


Dim VBCodeMod As Object 'CodeModule
Dim LineNum As Long

With ThisWorkbook.VBProject
Set VBCodeMod = .VBComponents(.VBComponents.Count).CodeModule
End With

With VBCodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, _
"Sub ProcedureBBB()" & Chr(13) & _
" Msgbox ""Hello world!"", vbInformation, ""ProcedureBBB""" _
& Chr(13) & "End Sub"
End With


End SubWatch when you get the "Hello World" message for the version from OnTime, and when from Run.

I am not convinced that I am going the right way, but without looking at the whole solution I am unable to indicate why the procedure will be started twice.

Artik