Exporting Code?

[Koolaid99]

Ok, I'm having a real problems trying to export in Access 2000 to a spreadsheet. I'm trying to automate the process that I have been doing. First to explain the process now.

I have a spreedsheet and one sheet is called "Raw_Data". This sheet in the workbook was created by right clicking a query called "Raw_Data" and chosing export. Now I have multiple files and the data changes in the query. Every month I update the spreadsheets, but everytime I do it I still have to unhide the main window in the database and right click the query to do the export. It will overwrite the data and I'm done.

Now its not so bad except I have a criteria that works of a option button on a form. I would like to have a button that just does the export and does the exact same thing as the right click would.

I have tried the output to macro function except this overwrites everything in the spreadsheets and even deletes other sheets in the workbook. So then I tried the transfer to spreadsheet macro function. This one does leave the other sheets alone but it still has two problems. One being it doesn't prompt what file you would like to overide and two if you export 5 lines first time and 3 the next and try to export 5 only the first 3 show up.

Is there any module code I could use that easily simulates the right click function of export?

 

[tledwards]

Try using the DoCmd.OutPutTo

DoCmd.OutputTo acOutputQuery, "Raw_Data", acFormatXLS

This should prompt you for the file name a location to save the file to.

 

[Koolaid99]

That way will delete all sheets and create a new one with just the data. I have 3 sheets in the spreadsheet first one is filled by option buttons and macros that filter information from the third sheet that is rawdata. If I use that export the other 2 sheets are deleted.

 

[JeroenNL]

Give the TransferSpreadsheet a try. I think you should be able to give a range in the parameters also. Something like Sheet1! or Raw_data! for example.
Bye,
Jeroen

A 3d level editor in Delphi

http://www.gamefortress.com/~delgine

 

[JeroenNL]

Sorry, I didn't read the post properly and I see you've already tried the Transferspreadsheet function. To solve problem 1 , you could write a simple wrapper around the TransferSpreadsheet function. Something like (pseudocode):

public function WriteToExcel(filename as string)

filename = inputbox("What's your filename?&quot

docmd.transferspreadsheet ... filename+ other parameters

end function

Then use the Runcode function in a macro to call the WriteToExcel function and put the macro behind the Click event of a button.

I'm not sure about your second problem. If you specify the range, wouldn't it work properly?
Bye,
Jeroen

A 3d level editor in Delphi

http://www.gamefortress.com/~delgine

 

[Koolaid99]

Problem with the transferspreadsheet is it doesn't prompt you so you can find a file to replace the data. I would have to make a new one each time I added a new customer export.

I know that the right click export is a diffrent type of export as the other two. I thought there should be a way though to break down the menu commands and add a function call to a button that would run the same thing as the right click.

 

[Koolaid99]

replied at same time =) I'm not familiar on the actual module coding for the transferspreadsheet but will look it up and give it try, I'll be reporting back soon. Thanks

 

[Koolaid99]

That seems to be working, Is there a easier way to get the path then to manually type this in? Something like the open window look to it. If not I may just try to make a combo box and choose a selection from there. Adding a field won't be as hard as typing a location every time. Once isn't so bad.

 

[JeroenNL]

A combobox would be a good idea to solve the location problem. You can also store the path as typed in by the user in a seperate table. Let's call it tblINI; it can store various variables used by your program. At the start of your program, you load the fields found in tblINI in variables in a module. Then, in the WriteToExcel function, refer to those variables.

Pseudocode:

*at startup of your application
INIfilename = tblINI.Fields("Excelfilename&quot

*in the WriteToExcel function
public function WriteToExcel

INIfilename = Inputbox("What's your file?)
docmd.transferspreadsheet ... INIfilename+other parameters

end function

*when the application stops
tblINI.Fields("INIFilename&quot

But this solution requires a bit of coding so if you don't feel comfortable with code, this might not be what you're looking for. It is a solution though.

I don't know how to bring up the Open Window box in Access. If/when I find out, i'll repost.

Bye,
Jeroen
tbl

Bye,
Jeroen

A 3d level editor in Delphi

http://www.gamefortress.com/~delgine

 

[JeroenNL]

Oops, spotted a little error:

*when the application stops
tblINI.Fields("ExcelFilename&quot = INIFilename

Of course, INIFilename is defined something like this:

Dim INIFilename as string

Bye,
Jeroen

A 3d level editor in Delphi

http://www.gamefortress.com/~delgine

 

[Koolaid99]

I didn't really know how to get it in a module to work but I didn't really needed a module just added the following code to the actually button.

Dim filename As String

filename = CusFileLoc.Value

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Raw_Data", filename, True

I thought "filename = Form!export!cusfileloc" would work to get the value I was looking for but it didn't.


Thanks for the help, finally got this to work will save a lot of time.