RunCode error (97)

Topic

New Reply

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.

Reply | Quote

Replies

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

TrnsfrToXls()

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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????

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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….

Reply | Quote

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.

Reply | Quote