[RESOLVED] Call Excel Sub from VB6

[Jon_G]

I have a prog that opens up an excel workbook. I am trying to call a sub that is written in the excel workbook (vba) from my vb6 prog.

Is this possible?

[Bruce Fox]

Try calling the Macro (from within Vb6) like:

Call [ExcelObject]Module1.Macro1

[Jon_G]

so you can call a macro but not a sub on a worksheet?

[Bruce Fox]

Either/Or.

I'm just testing the theory now.

[Jon_G]

ya i tried that it doesnt wanna work for me

any other suggestions?

[Bruce Fox]

Worked for me:

Call objExcel.Sheets(1).Test_Sub

Please post the code your using open Excel from within VB (I'll mod it to suite)

[Jon_G]

Private Sub Command1_Click()
    Dim myExcelApp As Excel.Application
 
    Dim myExcelSheet As Excel.Worksheet
 
 
    ' Create the Excel application.
    Set myExcelApp = New Excel.Application
    
    'open workbook
    myExcelApp.Workbooks.Open "C:\Program Files\Iron Test Stand\Graph.xls"
    myExcelApp.Visible = True
    
    Set myExcelSheet = Sheets("New Graph")
    
    myExcelSheet.Select
    
    
        With myExcelApp.ActiveSheet
            .txtfilename.Activate
            .txtfilename.Text = "C:\Documents and Settings\Jon\Desktop\Iron Tests\2039\123.456-August 08, 2005.csv"
            'CALL CMDBROWSW_CLICK()
            'The above doesnt work HEEEEEEEEEEELPPPPP lol
            
           
        End With
    
End Sub

[Bruce Fox]

Try:

Option Explicit
 
Private Sub Command1_Click()
    Dim myExcelApp As Excel.Application
 
    Dim myExcelSheet As Excel.Worksheet
 
 
    ' Create the Excel application.
    Set myExcelApp = New Excel.Application
    
    'open workbook
    myExcelApp.Workbooks.Open "C:\Program Files\Iron Test Stand\Graph.xls"
    myExcelApp.Visible = True
    
    Set myExcelSheet = Sheets("New Graph")
    
    myExcelSheet.Select
    
    
        With myExcelApp.ActiveSheet
            .txtfilename.Activate
            .txtfilename.Text = "C:\Documents and Settings\Jon\Desktop\Iron Tests\2039\123.456-August 08, 2005.csv"
            CALL .CMDBROWSW_CLICK()
            'Note the . (Dot) preceeding the Sub!
            
           
        End With
 
  Set myExcelApp = Nothing
 
End Sub

[Bruce Fox]

I added Option Explicit, and released Memory (by Setin myExcelApp = Nothing)

[Jon_G]

Lol im retarded

I was calling Private Sub cmdBrowse_Click()

not Public

DOH

Thanks for the help coach! It works great!!!

[Bruce Fox]

Great (oops, I forgot to mention that my testSub was Public too..... sorry )

[Jon_G]

Well either way +5 Rep for you
thanks