Create a crosstab query in Access that gets data from Excel

[Philly44]

Hello,
I need to create a crosstab query that gets its from a spreadsheet. The data itself will depend on the which spreadsheet is used and further more which named range is used so I cannot import into premaid tables. This will eventaully used to create graphs. I have tried many different things but so far I have had no luck.
I am bringing in the data through an OLE object so I would like if I could use the same references(Global strings) if anyone can help I will be indebted to you.

Chris

 

[rewdee]

Hi Chris

If you have a name range this might help.

Sub MakeChart(strPath As String, strRange As String)

' Object variables for Automation stuff
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objResultsSheet As Excel.Worksheet
Dim objXLRange As Excel.Range


' Go ahead and create an object from the XLS file
Set objXLBook = GetObject(strPath)

' We can use the Parent
' property of the workbook object
' to get a pointer to Excel's
' Application object
Set objXLApp = objXLBook.Parent

' Set object references for the
' workbook's two worksheets
Set objResultsSheet = objXLBook. _
Worksheets("SomeWorksheetName&quot


' Let's start by clearing the data
' range on the results worksheet--
' we'll use the rngAllData range
Set objXLRange = objResultsSheet.Range(strRange)


objResultsSheet.ChartObjects(1). _
Chart.ChartWizard Source:=objXLApp. _
Union(objResultsSheet.Range("B2:C2&quot, _
objXLRange)


' Now lets save the workbook and
' shut down Excel when we're done
objXLBook.Save
objXLApp.Quit

' Let's clean up our act
Set objResultsSheet = Nothing
Set objQuerySheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

End Sub

This is a modified version from Access Developer's Handbook. It also assumes there's a chart object on the sheet. In your code, if you are developing a chart that is in Access, you will need to make reference to that one.

I hope this helps,
Bill N

 

[Philly44]

Thanx a lot I'm going to try it right now.