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

    RunCode error (97)

    Well if i can get this to work that would be great. I entered the code into a VB module in access as follows
    I recived it from another forum but something i am doing is wrong.


    Public Function TrnsfrToXls()

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Find All Issues by Selected Criteria2", "C:WINNTProfilesEdward SantevecchiDesktopFind All Issues By Selected Criteria2.xls"

    End Function


    All of the coding is on 1 line correct? The "Find All Issues by Selected Criteria" is not a table but a datasheet created by the query. Is that a problem, if so how would i change that to a table. Have i set the paths up incorrectly?

    My access macro first opens the query (Find All issues....) in DATASHEET view with the mode set to EDIT.

    Then I have output to my xls file (Find all ISSues...)

    I then use RunCode and use the line:
    Function Name = TrnsfrToXls(). {for some reason access says that i have entered the function name incoorectly and it can not find it, have i used an improper format. I thought it was function name = function name().

    Next i close the query. What am i doing wrong with the function name? Why can't i run the code? Is there something dumb i am missing here with my procedures? Thank you and happpy new year.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunCode error (97)

    In the Macro you don't have to use an equal sign.
    Just the name of the function and parantheses

    TrnsfrToXls()
    Francois

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

    Re: RunCode error (97)

    Ok that'll run it but im still getting an error, it says:

    Run time error 3274
    External table isnt in the expected format


    what to do

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunCode error (97)

    I tried but I can't reproduce the error.
    A few questions :
    Why are you opening the query before exporting it ?
    If you don't open it can you run the macro ?
    Can you execute the code from within the design view of the module ?
    To execute the function, put the cursor anywhere in the function and press F5.
    Francois

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

    Re: RunCode error (97)

    OK here's the code again:

    Private Function TrnsToXls()

    DoCmd.TransferSpreadsheet 1, 5, "Find All Issues By Selected Criteria2", "C:WINNTProfilesEdward SantevecchiDesktopFind All Issues By Selected Criteria2.xls"

    End Function

    I can not execute the function with the F5, same error. When i try to run a macro that consists of RunCode, using TrnsToXls() as the function name I get the error that Access can not find the expression I entered.

    I am using this code to copy what is in acess from the query material chosen and then send it to excel. When you export to excel from acess it will only handle 255 characters. Some of my fields in access have 500+ characters so i lose half of the field on the export. When the user selects the criteria for the query on a form and hits the command button the query checks for what was selected (thus it is opened), outputs the data to an excel file, runs this code, closes query. I The reason i output to excel before the code is run is to leave the user at the excel file they are exporting to. I will eventually make this file a template file so all data would go it and then get saved as something else, preserving the code destination. If i runt he code w/o the output to excel the same errors still occur.

    What to do? Do you knwo of any other way to export data to excel automatically. I know a paste special, as unicode will work from access to excel but i can not get that to work automatically. Thank you. Have i stumped you completly yet????

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: RunCode error (97)

    Does it have any effect if you rename your query and the output file to exclude the spaces?
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: RunCode error (97)

    Could you clarify what you're trying to do here? A Private function can't be called externally, only from within the module that contains it. A public function within a class (including a form) can only be called from within that class OR by referencing the class object as well as the routine.
    Charlotte

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

    Re: RunCode error (97)

    ended up working after i deleted the file first then let the code create the file, don't ask me why. working fine for now thanks. sorry about the multiposts before. didnt know everything was connected like that.

    However, I would like to have that excel file open after the export takes place. I added a line that i've used in modules to open excel but i get variable not defined.

    Private Sub cmdExp_Click()

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Find All Issues by Selected
    Criteria2", "C:WINNTProfilesEdward SantevecchiDesktopFind All Issues By Selected Criteria2.xls"

    'Open Dummy File
    appExcel.Workbooks.Open "C:WINNTProfilesEdward SantevecchiDesktopFind All Issues By
    Selected Criteria2.xls"
    End Sub

    does appExcel.Workbooks.Open not work with the _click event??? ANy other way? Thanks

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

    Re: RunCode error (97)

    That whole private function thing was just a mistake. But if i did make it a public function how would i use it in a macro so that RunCode would work? I've tried RunCode, function name TrnsToXls() but it will not work. humm....

Posting Permissions

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