How to run macro when call in visual basic? 1

[babysophia]

Hi Everyone,

Does anyone knows how to call/run macro using visual basic 6.0?

I have an existing document with macro on it. I've tried to make a new project in visual basic 6.0. I've created new button in the form. If I click that button, it should automatically call the document and should automatically run the macro when opening that document instead of clicking the tools--->macro-->macros-->run. Please help me...

Thanks guys!!! =)

 

[ClulessChris]

Set your macro to run on open, set your document as a template, open file from template?

Everybody body is somebodys Nutter.

 

[babysophia]

Thanks for replying. I'm new to macro and I'm having a problem opening it. Here's my code below and the error I'm getting is "Main() Error -2147352573 - Unable to run the specified macro". Tell me if it's right. Thanks.

Application.Run MacroName:="Fumiguide Report.AutoNew.Main"

I inserted the code in my Main Function:

Sub Main()
'**************************************************************
' DESCRIPTION
' This subroutine is the first sub that will be called by
' the macro
'
' PARAMETERS
' NONE
'
' RETURNS - None
'
' REVISION LOG
' DATE NAME CHANGE
' May 2, 2003 Joseph Manyaga Initial Revision
'**************************************************************

Dim sFileName As String
Dim sLine As String
Dim arrHeading As Variant
Dim sArea As udtAreaInfo
Dim bSuccess As Boolean

Dim arrFields As Variant
Dim arrFieldValues As Variant
Dim nCount As Integer

'These are the conversion factors
Dim dVolumeFactor As Double
Dim dTemperatureFactor As Double
Dim dWeightFactor As Double

Dim sErrorString As String

Const FUNCTION_NAME = "Main()"

On Error GoTo Error_Handler

bSuccess = False
bNoDosage = True

'Application.Run MacroName:="Normal.AutoNew.Main"
Application.Run MacroName:="Fumiguide Report.AutoNew.Main"

'Get the file name via the File Open dialog box
sFileName = WordApplicationGetOpenFileName("*.txt", True, True)

If sFileName = "" Or Right(sFileName, 1) = "\" Then
'User did not select any file, ergo abort!!!
Exit Sub
End If

'Open the file
nFileNum = FreeFile
Open sFileName For Input As #nFileNum

frmUnitOptions.Show

'Initialize the units
If bIsMetric Then
sVolumeUnit = " cu m"
sTemperatureUnit = "°C"
sCTUnit = " g-hr/cu m"
sWeightUnit = " kg"
sConcUnit = " g/cu m"
sTimeUnit = " hrs"
Else
sVolumeUnit = " cu ft"
sTemperatureUnit = "°F"
sCTUnit = " oz-hr/MCF"
sWeightUnit = " lbs"
sConcUnit = " oz/MCF"
sTimeUnit = " hrs"
End If

'Loop through the file while it is not end-of-file
Do
'Get the next line
Line Input #nFileNum, sLine

'I use the name arrHeading because I know that we will only get the
'section heading in this line.
arrHeading = MySplit(sLine, vbTab)

'If the value is not blank and contains only a single value, then
'this is a section header.
If sLine <> "" And UBound(arrHeading) = 0 Then

'Now we have to determine which section we are in
Select Case sLine
Case SEC_CUSTOMER_INFO
bSuccess = DisplayCustInfo

Case SEC_JOB_INFO
bSuccess = DisplayJobInfo
If bNoDosage Then
Exit Do
End If

Case SEC_AREA_INFORMATION
bSuccess = DisplayAreaInfo

Case SEC_INTRO_PLAN
bSuccess = ReadUselessData

Case SEC_MONITOR_PLAN
bSuccess = ReadUselessData

Case SEC_INTRO_HISTORY
bSuccess = DisplayIntroductionHistory

Case SEC_MONITOR_POINT_INPUT
bSuccess = DisplayMonitoringDataInput

Case SEC_MONITOR_POINT_STATUS
bSuccess = ReadUselessData

Case SEC_AREA_STATUS
bSuccess = DisplayAreaStatus

Case SEC_JOB_PEST
bSuccess = DisplayJobPest

Case Else
'Do nothing

End Select

If Not bSuccess Then
Exit Do
End If

End If

Loop While Not EOF(nFileNum)

Close #nFileNum

If Not bSuccess Then
MsgBox "The Fumiguide Report was not created successfully.", vbCritical
Else
MsgBox "The Fumiguide Report was created successfully.", vbInformation
End If

'Go to the first field
ActiveDocument.Bookmarks("BeginCursor").Select

Exit Sub

Error_Handler:
sErrorString = FUNCTION_NAME & vbCrLf & vbCrLf & "Error: " & Err.Number & " - " & Err.Description
MsgBox sErrorString, vbCritical, "Error"

End Sub

 

[babysophia]

How can I set my macro to run on open, set it as document and open the file from template? Is it from the code?

Here's my code in vb below: This will just open the document but needs to run the macro instead of automatically opening it.

Private Sub Command3_Click()

Set wrd = New Word.Application
wrd.Visible = True
wrd.Documents.Open "C:\ProfumeProject\Fumiguide Report.dot"
Set wrd = Nothing

End Sub

 

[MzKitty]

This is an example of how I run an Access Macro:
Dim strCaption As String
Dim AccessApp As Access.Application
adoDatabase = strDrive & ":\ACCESS\CUSTOMER.MDB"

strCaption = ""
lblCommand1.Visible = False
frmUpdate.MousePointer = 11
Set AccessApp = CreateObject("Access.Application")
AccessApp.Visible = False
AccessApp.OpenCurrentDatabase (adoDatabase)
AccessApp.DoCmd.RunMacro "BRING IN NEW CUSTOMERS MACRO"
AccessApp.Quit acQuitSaveAll
'AccessApp.CompactRepair ("G:\ACCESS\CUSTOMER.MDB")
frmUpdate.MousePointer = 0

strCaption = "Customer Database has been Updated. "
lblCommand1.Caption = strCaption
lblCommand1.Visible = True

Cathy

 

[ClulessChris]

Sorry, I assumed your Macro was in Word or Excel.

Everybody body is somebodys Nutter.

 

[babysophia]

Thanks for replying Cathy and ClulessChris!

My Macro was in Word. I just want to open it automatically without clicking the run command in Microsoft Word 2002 when called in VB 6.0. =)

 

[ClulessChris]

Reference your macro in the open event of the word document

Private Sub Document_Open()
    Call Main
End Sub

Then just open the document from VB the macro Should then run, But you'll still have the security warning (Enable Macros) message.

regards

Everybody body is somebodys Nutter.

 

[babysophia]

Hi ClulessChris,

It's working now. Thank you very much!!!

 

[babysophia]

O-oh!! I have a little problem out here again! I can't pass a parameter in which I saved my text file from my local. How can I pass a parameter to Macro?
I should pass the value from Visual Basic to Macro to open what I created in my desktop. Is it possible? I should display the records from the text file in Microsoft Word. =)

--Visual Basic 6.0--
Function ExportFile()
...
...
...
'Saving File to my local computer
strExportFilename = "C:\Documents and Settings\nai2151\Desktop\Test.txt"
End Function

Function OpenFumiguideReport()
Dim wrdApp
'Open the report macro
Set wrdApp = New Word.Application
wrdApp.Visible = True
wrdApp.Documents.Open "C:\Program Files\DAS\Fumiguide\Fumiguide Report.dot"
Set wrdApp = Nothing
End Function

--Macro in Microsoft Word 2002--
Function WordApplicationGetOpenFileName(FileFilter As String, ReturnPath As Boolean, _
ReturnFile As Boolean) As String
strFileName = strExportFilename -----> I should pass the value from Visual Basic to Macro to display what I've created in my desktop.
WordApplicationGetOpenFileName = strFileName
End Function

 

[ClulessChris]

I don't see how you could pass the parameter directly to the macro, however you could paste it into the body of the word do and have your macro code read (and clean up when done) this text.

Everybody body is somebodys Nutter.

 

[babysophia]

What do you mean paste it in the body of the word? Can you provide me a syntax for that? I already done it but I'm having a problem with my code.

 

[babysophia]

Hi guys! Thanks for replying. I already solved my problem. My another problem now is to rename the document as Document1 when I open the .dot file. Here's my code below in VB and Macro:

---VB 6.0---
I've placed this code in one of the modules in VB:
Function OpenReport()
Dim wrdApp
Set wrdApp = New Word.Application
wrdApp.Visible = True
wrdApp.Documents.Open "C:\Program Files\DAS\Report.dot"
wrdApp.ActiveDocument.ShowText (strExportFilename) '-----> This is just a path for the text file I need to open to pass it in Macro
Set wrdApp = Nothing
End Function

---Macro in MS Word 2002---
VB called this function to pass the location of the text I need to open from VB. I have placed this code in ThisDocument under TemplateProject:
Public Sub ShowText(strPath As String)
Call Main(strPath)
End Sub

This is the function called in the ShowText above. This function will just pass the value(path name) from the ShowText "strPath" to open the text file I've created in my local. The values found in the text file will populate all the values in each column/row in the word. The word will display the .dot file name in the upper left corner of the Word. All I need to do now in order for me not to save/modify the .dot file. I just need to rename it. Is this possible? I have placed this code in modules:
Sub Main(strPath)
Dim sFileName As String
...
...
Const FUNCTION_NAME = "Main()"

On Error GoTo Error_Handler

sFileName = strPath

If sFileName = "" Or Right(sFileName, 1) = "\" Then
'User did not select any file!!!
Exit Sub
End If

'Open the file
nFileNum = FreeFile
Open sFileName For Input As #nFileNum
...
...
...
...
'--->Maybe I can place the renaming of .dot file to .doc file here--->

If Not bSuccess Then
MsgBox "The Report was not created successfully.", vbCritical
Else
MsgBox "The Fumiguide Report was created successfully.", vbInformation
End If

'Go to the first field
ActiveDocument.Bookmarks("BeginCursor").Select

Exit Sub

Error_Handler:
sErrorString = FUNCTION_NAME & vbCrLf & vbCrLf & "Error: " & Err.Number & " - " & Err.Description
MsgBox sErrorString, vbCritical, "Error"

End Sub