Access to excel & graph vba code

[eoinm824]

Hi, I am relatively new to access, and have no real knowledge of vba. I have a query that is pulling data from a table. What I want is to run vba code when i open a specific form that will plot this data to excel for a specific range of values. Currently I have the following but I do not know how to specify the range eg cells A2:E2. Code below:

Private Sub Form_Load()
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myRange As Object



Set xl = CreateObject("Excel.Application")

sExcelWB = "F:\TEST.xls"

''This will overwrite any previous run of this query to this workbook
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CR", sExcelWB, True

Set wb = xl.Workbooks.Open(sExcelWB)


''Sheets are named with the Access query name
Set ws = wb.Sheets("CR")
Set myRange = ws.Range("A22")
Set ch = myRange.Charts.Add




xl.Visible = True
xl.UserControl = True
End Sub


As you can see I have attempted to specify the range but it doesnt work. when the code runs i am presented with a run time error message

any help would be fantastic

Thanks!

 

[Grumm]

First, can you add your code between [code][/code] ? That would make our lives easier.

Second, I do most of the excel stuff like this :

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim ch As Excel.Chart
Dim myRange As Excel.Range

and checking the Microsoft Excel... Object Library" in Tools menu -> References.

But both methods should work.

 

[eoinm824]

Thanks. The code you wrote with out defining things as objects doesnt work. the current implementation is as follows

Private Sub Form_Load()
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myRange As Object ''Excel.Range

Set xl = CreateObject("Excel.Application")

sExcelWB = "F:\TEST.xls"

''This will overwrite any previous run of this query to this workbook
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "CR", sExcelWB, True

Set wb = xl.Workbooks.Open(sExcelWB)


''Sheets are named with the Access query name
Set ws = wb.Sheets("CR")
Set myRange = ws.Range("A2:F2")
[COLOR="Red"]Set ch = myRange.Charts.Add
[/COLOR]

xl.Visible = True
xl.UserControl = True
End Sub

When I run the access query with the vba in it it presents a message that says run time error, object doesnt support this property or method. the code i have in red is also highlighted

 

[Grumm]

Ok I found this :
http://www.access-programmers.co.uk/forums/showthread.php?t=155633

and this :
http://www.access-programmers.co.uk/forums/showpost.php?p=742348&postcount=4

In the first link, i found that this code :

Set ch = ws.ChartObjects.Add(50, 60, 500, 300).Chart

Works instead of the red line in your code.

 

[Cronk]

As I don't work much with Excel objects, and consequently have limited knowledge of vba properties of the excel objects, I mostly find it easier to use the Excel macro recorder to generate the vba in Excel for the manual steps needed to do whatever - in your case generate a chart.

It's then a much easier job to copy/paste the code into Access, with modification as necessary, usually prefacing each line with the Excel object you instantiate in your Access vba procedure.

 

[Grumm]

It is hard to get excel vba code to access code. Some objects can't compile in access. One is a simple one like

thisSheet.Charts.add

If you try this in excel it will work without problems. Past this in access using excel.Worksheet objects and you only get errors.
It is tricky sometimes to find the correct function name to call in access.

 

[Cronk]

Grumm, my previous post in this thread, indicates you need to preface your code with the Excel object you instantiate in Access code.

So
thisSheet.Charts.addwon't compile, but

objxl.thisSheet.Charts.add

will.