Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can't run easy code? (off 97)

    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

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't run easy code? (off 97)

    You can try this<pre>Dim appexcel As Object</pre>

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

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't run easy code? (off 97)

    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

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't run easy code? (off 97)

    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.

    <pre>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
    </pre>

    Deb <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can't run easy code? (off 97)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't run easy code? (off 97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •