Newbie:export to Excel 
Author Message
 Newbie:export to Excel

I have the following code which transfers the results of
an Access query to an Excel spreadsheet, draws 5 graphs
and then starts up Excel to view the results.

However, I don’t want to specify where the excel workbook
saves the file or its name…I want users to be able to give
the file a name of their choice and decide where to save
it when they close the workbook. How do I adapt this code?
Thanks

Public Function CreateChart (strSourceName As String,
strFileName As String)

Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlChartObj As Excel.Chart
Dim xlSourceRange As Excel.Range
Dim xlTitleRange As Excel.Range

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel97, strSourceName, strFileName, False
Set xlApp = CreateObject("Excel.Application")
Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
Set xlSourceRange = xlWrkbk.Worksheets(1).Range("A:A, B:B")
Set xlChartObj = xlApp.Charts.Add

….. build charts etc

xlApp.Visible = True

End Function



Wed, 07 Dec 2005 04:43:29 GMT  
 Newbie:export to Excel
How about

xlApp.Dialogs("xlDialogSaveAs").Show

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Quote:
> I have the following code which transfers the results of
> an Access query to an Excel spreadsheet, draws 5 graphs
> and then starts up Excel to view the results.

> However, I don’t want to specify where the excel workbook
> saves the file or its name…I want users to be able to give
> the file a name of their choice and decide where to save
> it when they close the workbook. How do I adapt this code?
> Thanks

> Public Function CreateChart (strSourceName As String,
> strFileName As String)

> Dim xlApp As Excel.Application
> Dim xlWrkbk As Excel.Workbook
> Dim xlChartObj As Excel.Chart
> Dim xlSourceRange As Excel.Range
> Dim xlTitleRange As Excel.Range

> DoCmd.TransferSpreadsheet acExport,
> acSpreadsheetTypeExcel97, strSourceName, strFileName, False
> Set xlApp = CreateObject("Excel.Application")
> Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
> Set xlSourceRange = xlWrkbk.Worksheets(1).Range("A:A, B:B")
> Set xlChartObj = xlApp.Charts.Add

> ….. build charts etc

> xlApp.Visible = True

> End Function



Wed, 07 Dec 2005 05:00:21 GMT  
 Newbie:export to Excel
Othewise, look at www.mvps.org/access/api to see how to open a dialog box in
Access.

Ronald's answer is probably much easier.

Chris


Quote:
> How about

> xlApp.Dialogs("xlDialogSaveAs").Show

> --
> Ronald R. Dodge, Jr.
> Production Statistician
> Master MOUS 2000


> > I have the following code which transfers the results of
> > an Access query to an Excel spreadsheet, draws 5 graphs
> > and then starts up Excel to view the results.

> > However, I dont want to specify where the excel workbook
> > saves the file or its nameI want users to be able to give
> > the file a name of their choice and decide where to save
> > it when they close the workbook. How do I adapt this code?
> > Thanks

> > Public Function CreateChart (strSourceName As String,
> > strFileName As String)

> > Dim xlApp As Excel.Application
> > Dim xlWrkbk As Excel.Workbook
> > Dim xlChartObj As Excel.Chart
> > Dim xlSourceRange As Excel.Range
> > Dim xlTitleRange As Excel.Range

> > DoCmd.TransferSpreadsheet acExport,
> > acSpreadsheetTypeExcel97, strSourceName, strFileName, False
> > Set xlApp = CreateObject("Excel.Application")
> > Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
> > Set xlSourceRange = xlWrkbk.Worksheets(1).Range("A:A, B:B")
> > Set xlChartObj = xlApp.Charts.Add

> > .. build charts etc

> > xlApp.Visible = True

> > End Function



Wed, 07 Dec 2005 05:18:31 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. NEWBIE - Exporting To Excel

2. Newbie Need help exporting to excel

3. Newbie question: How to export Outlook Express Block Senders list to Excel

4. Crystal Enterprise Exports to Excel 5 format instead of Excel 7

5. Exporting recordset into Excel and opening Excel

6. Newbie : Save (Export) database file

7. Newbie trying to read DLL exports from within VB

8. Newbie - exporting from VB to eVB

9. Newbie - exporting from VB to eVB

10. Exporting Reports to Excel in RT

11. Exporting a table to Excel

12. trouble importing AND exporting between access and Excel

 

 
Powered by phpBB® Forum Software