Sending Access data to Excel 1

[skyline16]

I have created a form with a command button that has the following code:

Private Sub cmdXL_Click()
Call CreateExcelChart
End Sub

There is a module called CreateExcelChart. The module is public and should be run when you click on the command button.

The following error message appears after clicking on the button:

Compile Error:
Expected variable or procedure, not module

When the module is run directly the following error message appears:
-2147217900: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'.

I copied the code from a sample database and changed the query name to match my query.

Any ideas??

 

[PHV]

Expected variable or procedure, not module
The module shouldn't have the same name as the Sub ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scottian]

so whats the code in the module?



"Its not who you are that defines you, its what you do..." - Bruce Wayne

 

[skyline16]

Option Compare Database
Option Explicit

Private Const conQuery = "qry_count_buildingcode"
Private Const conSheetName = "WO by building"

Public Sub CreateExcelChart()

Dim rst As ADODB.Recordset

' Excel object variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlChart As Excel.Chart

Dim i As Integer

On Error GoTo HandleErr

' Create Excel Application object
Set xlApp = New Excel.Application

' Create a new workbook
Set xlBook = xlApp.Workbooks.Add

' Get rid of all but one worksheet
xlApp.DisplayAlerts = False
For i = xlBook.Worksheets.Count To 2 Step -1
xlBook.Worksheets(i).Delete
Next i
xlApp.DisplayAlerts = True

' Capture reference to first worksheet
Set xlSheet = xlBook.ActiveSheet

' Change the worksheet name
xlSheet.Name = conSheetName

' Create recordset
Set rst = New ADODB.Recordset
rst.Open _
Source:=conQuery, _
ActiveConnection:=CurrentProject.Connection

With xlSheet
' Copy field names to Excel
' Bold the column headings
With .Cells(1, 1)
.Value = rst.Fields(0).Name
.Font.Bold = True
End With
With .Cells(1, 2)
.Value = rst.Fields(1).Name
.Font.Bold = True
End With

' Copy all the data from the
' recordset into the spreadsheet.
.Range("A2").CopyFromRecordset rst

' Format the data
.Columns(1).AutoFit
With .Columns(2)
.NumberFormat = "#,##0"
.AutoFit
End With
End With

' Create the chart
Set xlChart = xlApp.Charts.Add
With xlChart
.ChartType = xl3DBarClustered
.SetSourceData xlSheet.Cells(1, 1).CurrentRegion
.PlotBy = xlColumns
.Location _
Where:=xlLocationAsObject, _
Name:=conSheetName
End With

' Setting the location loses
' the reference, so you must
' retrieve a new reference to the chart.
With xlBook.ActiveChart
.HasTitle = True
.HasLegend = False
With .ChartTitle
.Characters.Text = conSheetName & " Chart"
.Font.Size = 16
.Shadow = True
.Border.LineStyle = xlSolid
End With
With .ChartGroups(1)
.GapWidth = 20
.VaryByCategories = True
End With
.Axes(xlCategory).TickLabels.Font.Size = 8
.Axes(xlCategoryScale).TickLabels.Font.Size = 8
End With

'Display the Excel chart
xlApp.Visible = True

ExitHere:
On Error Resume Next
' Clean up
rst.Close
Set rst = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub

HandleErr:
MsgBox Err & ": " & Err.Description, , "Error in CreateExcelChart"
Resume ExitHere
End Sub

 

[PHV]

And what is the name of the module (hopefully NOT CreateExcelChart ...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline16]

I changed the name of the module from 'CreateExcelChart' to 'ExcelBuilding'.

Now the error message is

-2147217900: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'.

 

[PHV]

Which line of code highlighted when in debug mode ?
What is the underlaying SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline16]

It never goes to debug mode. After clicking OK on the error message, it returns to screen with nothing highlighted. I'm not familiar with 'underlaying SQL code. Where do I look?

 

[PHV]

After clicking OK on the error message
No Debug button here ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline16]

Nothing. Just closes error message and returns to screen.

 

[CautionMP]

skyline16,
Routine has an error handler so no debug. Suspend the error handling to see the debug prompt (VBE -> Tools -> Options -> General tab, Break on all errors).

I'm guessing it will break here

...
    ' Create recordset
    Set rst = New ADODB.Recordset
    [highlight]rst.Open _[/highlight]
     [highlight]Source:=conQuery, _[/highlight]
     [highlight]ActiveConnection:=CurrentProject.Connection[/highlight]

Either the syntax of this statement or the syntax of [tt]qry_count_buildingcode[/tt].

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[skyline16]

You were right!! Do you think it has to do with the connection?
When I pass my cursor over the last line this box appears:
'CurrentProject.Con...="Provider=Microsoft.Jet.OLEDB.4.0,
User ID=Admin;Data...'

I copied the tbls,queries,module etc. from the sample database and tried to run the code and got the same error. Therefore I don't think it is a problem with the query syntax.

Thanks so much.

 

[PHV]

Replace conQuery with the SQL code of qry_count_buildingcode

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CautionMP]

skyline16,
Starting at step 1, can you open [tt]qry_count_buildingcode[/tt] by itself without an error?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[skyline16]

CMP,

qry_count_buildingcode opens without error. It returns two columns: one with the name of the building and one with the count of the number of work orders for that building.