Scottgem,

Here is the sample database & associated excel file relating to my problem w/ exporting from access to excel (thread started in AllExperts.com); you'll need to go into the VBA of the Access database and change the reference to the location of the excel file in order for it to run (I've marked the locations in the code). You should be able to start by clicking on all of the checkboxes in "form1" of the database "dbsample.mdl", and clicking "execute" and it will automatically run the query and transfer the associated fields to excel. Then you'll need to close the spreadsheet before running the query again. Then run the query again by clicking on 2 or 3 of the checkboxes and it should run fine. Then if you run the query again, clicking on all 4 checkboxes, an "error 3190: Too many fields defined" should come back. That's what I'm stuck at.

Thanks in advance to any help you can provide,
Heidi

Attached Files

sampleDB.zip (58.2 KB, 11 views)

ScottGem

Found it. The problem was your named range (quer) in the worksheet was set to two columns. It would work as long as only 2 boxes where checked. But once you checked another box. It bombed. I increased the range to the E column and it worked fine.

kenzie416

I tried increasing the named range (quer) to 8 columns, and it worked for the next time i ran the query with all 4 boxes checked, but it redefined quer to accomodate the 5 columns i needed. When i ran the query again for 2 checkboxes, is again redefined quer for the 3 columns that i needed. Then I ran it again w/ all 4 boxes checked and it bomb. It seems like I can decrease the number of columns needed and it will defined the named range fine, but increasing the number of columns is a problem.

ScottGem

Ahh I didn't test enough. Something is causing the quer range to fit the exported data. Once its less than 4, it won't allow more than what its set for.

One solution might be to redefine the quer range when the worksheet is closed. or after the import is finished.

kenzie416

i was thinking somewhere along those lines...in fact, i was trying something along those lines earlier today w/ no success. do you know how to do this in Excel VBA? If not, I can check w/ an Excel expert.

ScottGem

Try the people at utteraccess.com (another site I hang at). There are a few Excel MVPs there who may be able to help further. My Excel VBA is limited.

kenzie416

i pinged an Excel guy and got it worked out. Thanks much!

ScottGem

What was the solution?