Can’t run easy code? (off 97)

Topic

New Reply

I have the following code. it should display a message box asking whether formatting has taken place? if it has then it should run a macro in an xls file. this occurs with amodule in access.

Sub IsFormated()
Dim Answer As Integer
Answer = MsgBox(“Is the file formated ?”, vbYesNo, “Format”)
If Answer = vbYes Then

Dim appExcel As Excel.Application ””’error here”’

Set appExcel = CreateObject(“Excel.Application”)
appExcel.Visible = True
appExcel.Workbooks.Open “V:Risk_Mgmt_CreditCanada monitoringMacroHolder.xls”
appExcel.Run “Module1.Format_Report”
appExcel.Workbooks(“report144alll.xls”).Close True

End If
End Sub

the error is a user defind type not defined. what do i do?
please help

Reply | Quote

Replies

You can try this

Dim appexcel As Object

or, you can click Tools | References and select the MS Office X Object Libarary.

Reply | Quote

hi there i tried your fix with the ‘as object’ the thing is that in my switchboard i set the run code as the command. What exactly do i enter in the code name. do i use the module name i savbed it as “TextFormat” or do i use what the sub is called in the programming itself “IsFormated()” what would the exact text look like. is it better to try and run the module with a macro and use the switchboard to run the macro instaed of run code? thanks alot

Reply | Quote

It’s been a while since I used the switchboard… I seem to recall that using a macro was easier, but perhaps someone with more recent experience can chime in here?

Reply | Quote

The code behind the switchboard can execute VBA code easily. In the Switchboard Manager, supply the name of a public procedure (sub) or function without parentheses (the procedure or function can’t have arguments).

So if you have a function

Function MyFunc()
DoCmd.OpenForm “frmMyForm”
DoCmd.OpenReport “rptMyReport”
End Function

in a standard module, you can call it from a Switchboard button by setting the command to “Run program code” and the function to MyFunc

Reply | Quote

I took your code and ran it in Access after changing the workbook names to ones that I had. I then added the subroutine Format_Report to Module1. The only thing I changed as how I declared appExcel. It worked fine.

Sub IsFormated()
  Dim Answer As Integer
  Dim appExcel As Object
  
  Answer = MsgBox("Is the file formated ?", vbYesNo, "Format")
  If Answer = vbYes Then
    
    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = True
    appExcel.Workbooks.Open "d:modelinginventoryfinal.xls"
    appExcel.Run "Module1.Format_Report"
    appExcel.Workbooks("final.xls").Close True
  End If
  
End Sub

Deb

Reply | Quote