Issues with OutputTo and TransferSpreadsheet

[RZ15]

Hi,

I'm going to be giving many people an access database file with a switchboard with quite a few essential queries (query-by-form) so that they can pull data whenever they want. I have a button to export the file into excel as well. The issue I have is with this button.

I used to have this button running a macro that would OutputTo in Excel format. Since this limits me to around 16k records because of it outputting into microsoft excel 5.0, I could not use it. I then changed it to TransferSpreadsheet and I didn't put a path where it asked me, i just put the file name. This resulted in the file ending up in my documents (which is fine, because I can't put a full path since each user would not have that path). This is still an issue though since a user could have a large number of files in their my documents and it would be a nuisance to find it each time and if he has to export the same file another day and keep the old file, he would not have a choice to not overwrite the file.

What would be ideal is for that button to export into an excel version that allows the 65k rows and for the user to be asked where they want to put the file. This doesn't have to be the solution, but I can't leave it how it is. Can someone please suggest something?

I am using MS Access 2000 and MS Excel 2000.

Thanks in advance,

Raza

[RZ15]

This issue is slowing me down considerably. Right now I'm using TransferSpreadsheet, then a msgbox saying "the file is in C:\SomeFile.xls" and then the excel file running using RunApp. This can take up to 20 minutes for some queries. This is a LONG time. I need a remedy.

Help is appreciated.

[RZ15]

if anyone can give me ANY insight on this, it would be appreciated...

[missinglinq]

I can't really help you on the question of speeding up your transfer times; in point of fact I don't even have Excel on my main development machine! Don't ask; it involves a minor flood, my seldom use of spreadsheets and Micro$oft's ridiculous policy of encouraging registration of all software and then not allowing that registration to serve as proof of ownership!

But as to the naming of exported files so as not to overwrite previous files, I can offer a hack I've used in exporting files to other formats. The trick is to export the file to a relative subfolder (relative, that is, to the folder that the database itself is stored in) and to tack on a date or date/time stamp to the name of the exported file. Simply substitute TheFile for the path/filename in whatever method you use to export your file.

TheDate = Month(Date) & "-" & Day(Date) & "-" & Year(Date) & "-at-" & Format(Time, "hh-mm-ampm")
 
TheFile = CurrentProject.Path & "\YourSubFolderName\YourFileName-" & TheDate & ".xls"
 

CurrentProject.Path gives you the path to the folder where the database resides. If you wanted to store the exported files in the same folder as the db, instead of in a subfolder, you could use

TheFile = CurrentProject.Path & "\YourFileName-" & TheDate & ".xls"

Likewise, if only a date is desired you could omit the time portion of the name.

Hope this helps a little!

Linq ;0)>