Replies

We have changed our mission with ‘the monster’ please check out my new post:

http://www.wopr.com/cgi-bin/w3t/showflat.p…sb=5&o=0&fpart=

ends up the problem existed because the name of the sheet it was looking in had a ” ” it so excel thought i was calling a new directory when in fact that was just the name of the sheet, thanks for the help though.

Been there done that and i still get the same error that the folder/file i specified wasnt accessible or may be password protected. Then the pop up screen occurs and it lets me find the file, when i do i get a could not read file error. any clues?

Since the workbook was already open i tried

appExcel.Run “TopAlign”

But it says it can not find that macro, what is the correct path to set for the macro name?

And now i am editing this post to inform you i have finally fixed one of my own problems.

appExcel.Run “ThisWorkbook.TopAlign”

Hell just may freeze over tonight…..
Thanks

You were right as usual Legare, the wrap text was already doing what i needed. I decided to manually set all my cloumn widths and then just wrap the text. got rid of all the autofitting, how simple life may be. Thank you.

When i tried to make the links in excel. This is after they were outputed by access to their destination. ALL access does is output to that file. I am in excel and i hit = in a cell on one page and try to put a link to another excel file and then the errors occur.

I found this to do the trick nicely, thanks for the help

Public Function TrnsToXls()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, “Find All Issues by Selected
Criteria2”, “C:WINNTProfilesEdward SantevecchiDesktopFind All Issues By Selected Criteria2.xls”
End Function

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

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

OK charlotte,

So i think i have the part that checks each button and appropriate list box. Since each listbox has 2 possibel options (AND/OR) wouldn’t it look more like this:

strCriteria = strCriteria& MakeCriteria(lstBox1,LstBox1Op)
strCriteria = strCriteria & MakeCriteria(lstBox1,LstBox2Op)

OR am i doing something that says
IF lstbox1op = 1 then strCriteria = strCriteria& MakeCriteria(lstBox1,LstBox1Op)
IF lstbox2op =1 then strCriteria = strCriteria& MakeCriteria(lstBox1,LstBox2Op)

Siince it is a saved query what is this whole pasing of the parameters issue. Doesnt sound too tempting for me. I guess the biggest problem is what that function would look like, i am assuming it is a function called MakeCriteria and tells access to look in that listbox for the values selected and decide whether or not it is an AND/OR field to be included in the query? If i’m right (slim chance) how would you write something like that, are their reserved words that do this for you? Thank you.

They already are memo fields

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

Edited by gwhitfield on 01-Jan-02 22:19.

** cross-posted here **

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.

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

The query was made the normal way, i did not use code to create it. It is a basic query all i entered was teh field, table, show(check/uncheck), and the criteria and then repeated for each field. I think that is what you are looking for.

So i just add the radio buttons assign them names and do nothing woth them but place a caption for each that says “AND” and 1 that says “OR”. Alll the coding would be in my command button that executes the query. When entering the code i assume it would be:

Private Sub Command28_Click()

LstBox1Op = iif(optBox1,”AND”,”OR”) ‘ where LstBox1Op is the AND option button
LstBox2Op = iif(optBox2,”AND”,”OR”) ‘where LstBox2Op is also the AND option for the 2nd listbox

So I take it my OR codes that will be placed in my command button would be

LstBox3Op = iif(optBox1,”OR”, “AND”) ‘ where LstBox1Op is the 1st OR option button
LstBox4Op = iif(optBox2,”OR”,”AND”) ‘where LstBox2Op is the 2nd OR option for the 2nd listbox
??????

what the heck is this part doing:

strCriteria = MakeCriteria(lstBox1,LstBox1Op)
strCriteria = strCriteria & MakeCriteria(lstBox2,LstBox2Op)

My guess is it assigns the values in the 1st list box based on whther or not the option button is selected, to a string variable and then adds that to the selected options for the 2nd list box. Thus making a long string to perform the query. If there were more than 2 listboxes the next lines would read

strCriteria = MakeCriteria(lstBox1,LstBox1Op)
strCriteria = strCriteria & MakeCriteria(lstBox2,LstBox2Op)
strCriteria = strCriteria & strCriteria & (lstBox3,LstBox3op)
strCriteria = strCriteria & strCriteria & strCriteria & (lstBox4,LstBox4op)

I look at this and it makes no programming sense to me, i guess because i am not a good programmer. Am i on the right track at least?? So ill put this all into by command button on the event click and this will allow me to select multiple choices from a list box, decide which listboxes must(AND) / may(OR) be included in the query. Others have mentioned a foreign term to be called OLE that i will need, is this it? I hope so becuase i am lost enough as is. Thank you very much, happy new year!!!