running complicated Excel commands from Access (1 Viewer)

[Banista]

Hi everyone,
I am currently writing an Access module that first exports a table to Excel, then uses an Excel object for further formatting inside Excel.

After the file is exported and created (which works fine), I initialize an Excel object like this:

Set xlAnw = CreateObject("excel.application")
xlAnw.Visible = False
xlAnw.Workbooks.Open FileName:=ee_filename

which also works fine. Now, I can use basic Excel commands as I would expect, for example:

xlAnw.Sheets("DayAnalysis").Select
xlAnw.Rows("1:1").Select

However, now I am trying to create a diagram from scratch from the data already present in the sheet.
The recorded Excel macro code is as follows:

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Tagesanalyse").Range("A2:B254"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Tagesanalyse"
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Characters.Text = "Zugriffsverlauf"
ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Anzahl"
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Datum"
ActiveSheet.Shapes("Diagramm 1").IncrementLeft 4.5
ActiveSheet.Shapes("Diagramm 1").IncrementTop -109.5
ActiveWindow.Visible = False

Now this is where the trouble starts. I tried to run this code from Access in a basic

With xlAnw
   .Charts.Add
   .ActiveChart.[...]
   [...]
End With

environment, but Access cannot interpret the lines with a := correctly, and I get an "incompatible types" warning in the line "ActiveChart.ChartType = xlLine"

Intuitively, that actually makes sense, since "xlLine" is not defined in Access, and := could well clash with Access' syntax in such a case.

So my questions:
1) Is there a way to run such Excel commands from Access?
2) Failing that, is there a way to create, save and then run an Excel macro from scratch out of Access? (I know how to run an existing Excel macro from Access, but couldn't find anything on creating a new one. I'd prefer not to use additional files apart from the .mdb and target .xls. Temp files would be fine though)

Thanks in advance for any help you can provide, I've been scouring the net for the past 90 minutes but couldn't find any relevant info, and that was a pretty lousy way to spend a Friday afternoon in the office
Bani

 

[chergh]

I would recommend for you to pull data into excel from Access rather than push data into excel from access.

I will have a think though

 

[Banista]

well, the thing is, the Access application is an firm-internal tool I'm developing, so any solution that requires anything done manually from another file is more or less infeasible, as I'll have a hell of a time telling people how to use the thing

The whole point of automatically creating the diagram from Access is to save users time (so they dont have to create them themselves in Excel all the time).

Thanks though!

 

[chergh]

I would start of like:

dim xlchart as excel.chart

set xlchart = xlAnw.Charts.Add

with xlchart

.ChartType = xlLine
.SetSourceDate xlsheet.cells(2,1).CurrentRegion
.PlotBy = xlColumns
.location _
where:=xlLocationAsObject
Name:="Tagesanalyse"
end with

Then after this you would use activechart

with xlanw.activechart
.hastitle=True
.HasLegend = False
with .chartTitle
.Characters.Text = "Zugriffsverlauf"
.font.size = 12
end with

and so on

 

[MarkK]

I doubt the ":=" is causing a problem. This is legitimate VBA syntax for passing named parameters.
There are two ways to alleviate the problem with enumerated Excel constants that are not available in Access.
1) Set a reference to Excel, which makes the entire Excel object model available in your Access VBA modules. This is called early binding and also allows you to explicitly declare Excel objects like...

Dim xlApp as New Excel.Application
xlApp.visible = true
debug.print xlLine

2) Replace the enumaration constants in your code with the numbers they represent, or declare your own constant using the same name.

xlChart.ChartType = 4
  - or -
Public Const xlLine = 4
xlChart.ChartType = xlLine

 

[Banista]

hmm, both

Dim xlApp as New Excel.Application

and

dim xlchart as excel.chart

give me an error: user defined type not defined. Am I maybe missing something else entirely, which causes all these problems?

 

[chergh]

should be:

dim xl app as excel.application

and set xlapp = new excel.application

dim xlchart as excel.chart should be fine though. Have you included the excel object library in your references?

 

[MarkK]

Set a reference to Excel. Try This...

Dim xlApp As New Excel.Application

 

[Moniker]

The "user type not defined" means you don't have the Excel Reference set. In References, it will be called something like "Microsoft Excel X.0 Object Library" (where X is the version number).

If you type in Dim xlApp As Excel.Application and IntelliSense doesn't figure out "Excel" after you type Ex, then you don't have the reference set.

 

[Banista]

I was afraid it was a reference issue.

That means anyone who I give the .mdb to has to set that reference as well right? Anyway around that?

Thanks anyway for the huge help you guys have been so far!

 

[Moniker]

It doesn't mean they have to set any references. It means that they have to own Excel. So long as they have the same version of Excel as you, then that's it. You're in trouble if they don't have Excel as that reference won't make any sense. Assuming all your users have the same version of Office, then there's nothing for the users to do beyond use your DB.

 

[Banista]

thanks a bunch for your help guys, got it working now!