Trying to run an Excel macro from HTML

[smitty_64]

All,

I am very new to VBA and am having an issue. My Excel macro is as follows:

Public Sub ImportButton_Click() ' SAC Import Utility for Deck Upload
Dim Cnt As Integer, Chk As String

DestFile = ActiveWorkbook.Name
InputFile = Application.Dialogs(xlDialogOpen).Show 'Get input filename
If InputFile = False Then Exit Sub
InputFile = ActiveWorkbook.Name

When I run it from inside Excel everything is hunky-dorry, however when I am trying to run it from HTML it will not run.

Any help appreciated.

Greg

[dglienna]

Since you've posted in the VB forum, I'll throw up a quick sample that I found that shows how to open an excel app from within VB.

Option Explicit

Public xlAppTemp As Excel.Application
Public xlWorkBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Dim strDate$

Public Sub GenerateReport()
  On Error GoTo ErrHandler
  
  ' Creating Object for Excel File.....
  Set xlAppTemp = New Excel.Application
  
  ' Making it Invisible and non-Interactive.....
  xlAppTemp.Visible = False
  xlAppTemp.DisplayAlerts = False
	' Opening Template Excel File.....
  Set xlWorkBook = xlAppTemp.Workbooks.Open(App.Path & "\Book1.xls", , False)
  Set xlSheet = xlWorkBook.Sheets(1)

  ' Making Active to Worksheet 1.....
  xlSheet.Activate

  ' I am doing lot of things in it, but to provide you with example
  xlSheet.Cells(15, 1) = "This is my report 1"

  ' Formating Date to attach with new file name.....
  strDate = Format(Date, "yyyy-mm-dd")
  
  ' Saving excel file with new name on different folder.....
  xlWorkBook.SaveAs App.Path & "\Output" & strDate & ".xls"

Cleanup:
  ' Destroying Objects.....
  Set xlSheet = Nothing
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing
'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True
  xlAppTemp.Quit
  Set xlAppTemp = Nothing
  Exit Sub
ErrHandler:
'I presume this section comes after ErrHandler, in which case you will want to close the workbook without changes.
'(save happens just above if no error occurs)
  xlWorkBook.Close SaveChanges:=False
  Set xlWorkBook = Nothing

'The Visible and DisplayAlerts settings should be reset, as they can affect 'manual' use of Excel too.
  xlAppTemp.Visible = True
  xlAppTemp.DisplayAlerts = True

  xlAppTemp.Quit
  Set xlAppTemp = Nothing
End Sub

Private Sub Command1_Click()
  Call GenerateReport
  Beep
End Sub

[smitty_64]

My problem is that by building just a normal HTML link to the Excel file, and using the Excel file in an HTML-environment, the macro hangs on the Application.Dialogs(xlDialogOpen).Show statement.

When I run Excel from the Windows environment, the macro runs fine.

[smitty_64]

For the one person that answered me, you were heading down the right road, just didn't get there, and I didn't put enough of the code up to tell what it was actually doing.

Here is what I think it is doing. I believe that since I am opening one instance of Excel and expecting to pull information from another instance of excel, I first have to close the instance in VB for the initial active.worksheet. Then go to the next.

This may sound basic to everyone else, but it was really kicking my butt. I am not a VB programmer, but have programmed in many other languages. Think I may be able to fix it now though.

[smitty_64]

And keep in mind this was running Excel in an HTML environment where it had already opened one dialog of Excel to run the initial program.

[dglienna]

Post more of it to help others that find this link.