exporting to excel

[Mack2]

Hello Everyone.
I am exporting to excel from Access. The data is exporting just fine. But, Does anyone know if from access, after the data is exported, create charts through code? Basically, I want them to click a button in access, have the data export to excel along with charts. Thanks for your help

 

[jbpelletier]

Hi, i guess this should work:
but u'll need to create an excel file called macro.xls that contain a macro called CreateChart, to create the chart...
(menu,tools, macro, record) name it CreateChart then create the chart then (menu,tools, macro, stop) save the file and there u are.

sub test()
On Error Resume Next
Dim objXL As Object
Dim QryName As String

QryName = "bla bla bla"
Set objXL = CreateObject("Excel.Application")
objXL.Application.Visible = True
objXL.Application.Workbooks.Open GetDBDir & "\" & "macro.xls"
DoCmd.OutputTo acOutputQuery, QryName, acFormatXLS, "C:\Temp\" & QryName & ".xls", True

objXL.Application.Run "macro.XLS!CreateChart"
objXL.Application.Workbooks("macro.xls").Close
Set objXL = Nothing
end sub

jb

 

[jbpelletier]

i forgot...
in your createChart macro this could be usefull to get the last line 'where there is data' instead of using predefine range

ActiveCell.SpecialCells(xlLastCell).Row

for exemple:
ActiveChart.SetSourceData Source:=Sheets("sheet1").Range("A1:A7")

--->

ActiveChart.SetSourceData Source:=Sheets("sheet1").Range("A1:A" & cstr(ActiveCell.SpecialCells(xlLastCell).Row ) )

jb

 

[Mack2]

Thanks, I will try it later this afternoon. I once worked with a Margaret Pelletier in Grand Rapids Mi. Any relation?

 

[jbpelletier]

no, i dont think.. :|

 

[Mack2]

I have switched projects for a few days. So I will not get a chance to try right away. I will let you know when I do. Thanks for your help