Export query from a switchboard

[dab1477]

I'm somewhat new to Access. Thanks for the assist in advance.
I have a database where I would like to graph the results of a query in
EXCEL. The Graph does not appear well within the Chart function of a report.
At present I run the query and copy and past into EXCEL and then create my
chart.
I would like to place a command on my switchboard menu that will export the
query to EXCEL. I know if I can create a macro or a VBA module, I can so
this from the menu I've created. I have unsuccessfully attempted to create a
macro and I don't know how to code in order to do export. I would appreciate
any direction that anyone can provide. Thank you.

 

[Ken Snell \(MVP\)]

Check out TransferSpreadsheet in VBA help. It can be used in VBA or macro;
it exports a table or query into an EXCEL file.

 

[dab1477]

Thanks Ken. I attempted to assign the TransferSpreadsheet command to a
button, such that a person could click the button and have the resulting
query transferred to a spreadsheet. I used the following code:

Private Sub Command3_Click()
Select Case fraExportType
Case 1
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"weekly ftq by machine center", "e:\dave\caledonia\Rockford
2008\FTQTest.xls", True
End Select
End Sub

When I click my command button, I don't get anything. Is there something
different that I should do? I have been unsuccessful in my attempts for the
last few days. Any direction would be appreciated. Thank you.

 

[Ken Snell \(MVP\)]

Where do you set a value for the fraExportType variable? If you don't set it
to a value of 1, it'll have a default value of 0. And your code then will
not do the DoCmd.TransferSpreadsheet action.

Also, change acSpreadsheetTypeExcel97 to acSpreadsheetTypeExcel9 in your
code.

 

[dab1477]

Thanks Ken. I'm new to this so I don't know how to set the variable. I did
find a "work around". I created a MACRO using the TransferSpreedSheet
function - thanks for pointing me to the TransferSpreadSheet link, now when I
assign the macro to a command button, it properly exports the file to the
correct directory, but it does so as an .ldb extension. My version of Excel
will not open this extension type. I get the following error message:
"my file".ldb cannot be accessed. The file may be read-only, or you may be
trying to access a read-only location, Or the server the document is stored
on may be not responding.
I have verified that the server location is NOT read-only. I can access and
alter other files within the same path, so the issue can't be a Read-Only
issue. Also since I can access and alter files within the path, I don't think
it's a server response issue. Any other ideas. I tried to alter the extension
to .xls without success. I'm excited that the Transfer function works. I just
need to read the resulting file! Thanks for all your help to date.

 

[Ken Snell \(MVP\)]

Do you really need a variable? Or will you always want to export when you
click the button? If you always want to export, then change the code to
this:

Private Sub Command3_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"weekly ftq by machine center", "e:\dave\caledonia\Rockford
2008\FTQTest.xls", True
End Sub


As for getting an '.ldb' extension, that simply is not possible with
TransferSpreadsheet. It always will make a file with '.xls' extension.

Post the code you're now using, and let's see wat you have.

 

[dab1477]

Thanks for the help to date. I have a better understanding. Is there an
explanation that details WHEN to use TransferSpreadSheet or Outputto? What is
the difference. You have been a GREAT help. My problem is resolved, but I did
not use TransferSpreadSheet. I will continue to explore mthat option. Thanks
again.

 

[Ken Snell \(MVP\)]

Use TransferSpreadsheet whenever you want the ability to "have" the features
/ design limits of EXCEL 97, EXCEL 2000, EXCEL 2002, or EXCEL 2003. OutputTo
will give you the features of EXCEL 95 -- for example, only 16384 records
per spreadsheet, and no text strings longer than 255 characters.