reference to a non-shared member requires and object reference

[nacco]

Hey all,

I have a problem with VB.NET and Excel.
I'm creating an Excel sheet which goes fine except for setting the auto filter on:
xlSheet.Range("A3", "R3").AutoFilter()

The message I receive is:Reference to a non-shared member requires an object reference. but all other setting on the sheet work fine.(with reference to the sheet).

Part of my code is:
xlSheet.Activate() 'I have more sheets
xlSheet.Name = Left(chkItem.Text, 4)

xlSheet.Range("A3").Value = "Truck"
xlSheet.Range("B3").Value = "Truck description"
............ and more headings to R3...
With xlSheet.Range("A3", "R3").Font
.Name = "Arial"
.Size = 10
.Bold = True
.Color = RGB(255, 0, 0)
End With
xlSheet.EnableAutoFilter = True
xlSheet.Range("A3", "R3").AutoFilter() 'this one goes wrong

thanks guys.

 

[ThatRickGuy]

How is xlSheet defined?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[sin1965]

need to see complete function code including signature

 

[chrissie1]

are you using vb.net 2005 ad is this a warning?

otherwise it should be excell.autosheet.autofilter or something like that. for the exact wording I should see the declaration of xlsheet.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[nacco]

thanks guys for your response.

I'm using VB.NET 2003
here comes my complete code, hope this will give you all the info:

Public Class WebForm1
Inherits System.Web.UI.Page

' SQL connectie naar Masterparts
Protected conMasterParts As ADODB.Connection ' Connectie
Friend rsMasterParts As ADODB.Recordset ' Recordset

' Algemeen
Friend chkItem As ListItem
Friend strQry As String
Friend intAantalExcelSheets As Integer
Friend intTotalSheets As Integer

' Excel
Public Shared xlApp As Excel.Application
Public Shared xlBooks As Excel.Workbooks
Public Shared xlBook As Excel.Workbook
Public Shared xlSheet As Excel.Worksheet

' Data Tabel
Friend dt As New System.Data.DataTable
Friend dr As System.Data.DataRow

Protected WithEvents CheckAll As System.Web.UI.WebControls.CheckBox
Protected WithEvents CheckBoxListTruck As System.Web.UI.WebControls.CheckBoxList
Protected WithEvents MsgLabel As System.Web.UI.WebControls.Label
Protected WithEvents ButtonDownload As System.Web.UI.WebControls.Button
Protected WithEvents buttonGenerateFile As System.Web.UI.WebControls.Button


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'initialize the page here

If Not IsPostBack Then
If Not OpenConnectionMasterParts() Then
MsgLabel.Text = "Due to technical problems this site is temporarily not available"
End If
Session("SelectedDealerBrand") = "HYST" ' of "YALE"
Session("hlpDealerCode") = 35808
buttonGenerateFile.Enabled = True
ButtonDownload.Enabled = False
LoadTrucks()
End If

End Sub

Function OpenConnectionMasterParts() As Boolean
Try
conMasterParts = New ADODB.Connection
conMasterParts.ConnectionString = "Provider=SQLOLEDB;Data Source=ENNNT18;Initial Catalog=MasterParts;Integrated Security=SSPI"
conMasterParts.Open()
conMasterParts.CursorLocation = ADODB.CursorLocationEnum.adUseServer
OpenConnectionMasterParts = True
Catch
OpenConnectionMasterParts = False
End Try
End Function

Public Function ExecuteQuery(ByVal strQry As String) As Boolean
Try
Dim cmd As New ADODB.Command
cmd = New ADODB.Command
cmd.ActiveConnection = conMasterParts
cmd.CommandText = strQry
rsMasterParts = cmd.Execute()

ExecuteQuery = True
Catch exception As Exception
MsgLabel.Text = exception.ToString()
End Try
End Function

Sub LoadTrucks()
Try
If Session("SelectedDealerBrand") = "HYST" Then
strQry = "SELECT TruckCode, TruckDescription " _
& "FROM Truck " _
& "WHERE TruckBrand = 'HYSTER' " _
& "ORDER BY TruckCode ASC"
Else
strQry = "SELECT TruckCode, TruckDescription " _
& "FROM Truck " _
& "WHERE Truck.TruckBrand = 'YALE' " _
& "ORDER BY Truck.TruckCode ASC"
End If

ExecuteQuery(strQry)

' vullen recordset in DataTable, deze aan de CheckBox koppelen
rsMasterParts.MoveFirst()
CreateDataSource()
CheckBoxListTruck.DataSource = dt
CheckBoxListTruck.DataTextField = "TruckOmschrijving"
CheckBoxListTruck.DataValueField = "TruckCode"
CheckBoxListTruck.DataBind()

conMasterParts.Close()
conMasterParts = Nothing

Catch exception As Exception
MsgLabel.Text = exception.ToString()
End Try
End Sub

Function CreateDataSource() As ICollection
Try
dt.Columns.Add(New DataColumn("TruckOmschrijving", GetType(String)))
dt.Columns.Add(New DataColumn("TruckCode", GetType(String)))
dt.Columns.Add(New DataColumn("TruckDescription", GetType(String)))

Do Until rsMasterParts.EOF
dr = dt.NewRow()
dr(0) = rsMasterParts("TruckCode").Value & " - " & rsMasterParts("TruckDescription").Value
dr(1) = rsMasterParts("TruckCode").Value
dr(2) = rsMasterParts("TruckDescription").Value
dt.Rows.Add(dr)
rsMasterParts.MoveNext()
Loop
Catch exception As Exception
MsgLabel.Text = exception.ToString()
End Try
End Function

Private Sub CheckAll_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckAll.CheckedChanged

For Each chkItem In CheckBoxListTruck.Items
chkItem.Selected = CheckAll.Checked
Next

If CheckAll.Checked = False Then
CheckAll.Text = "Select all"
Else
CheckAll.Text = "Deselect all"
End If

End Sub

Private Sub buttonGenerateFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttonGenerateFile.Click
' check At Least 1 Truck Selected
Dim chkAL1TS As Boolean = False
' Dim TemplateFileName As String
Dim ExcelFileName As String
Dim oFile As System.IO.File

Try
'TemplateFileName = "C:\MpKp-Template.xls"
ExcelFileName = "C:\" & Session("hlpDealerCode") & ".xls"

' weggooien indien nog bestaat
Try
If oFile.Exists(ExcelFileName) Then
oFile.Delete(ExcelFileName)
End If
Catch
End Try

intTotalSheets = 0
For Each chkItem In CheckBoxListTruck.Items
If chkItem.Selected = True Then
chkAL1TS = True
intTotalSheets = intTotalSheets + 10
Exit For
End If
Next

If Not chkAL1TS = True Then
MsgLabel.Text = "Select one or more Trucks before generating Excel file"
Exit Sub
End If
MsgLabel.Text = "Creation of Excel file in progress, please wait...."

' Define Excel objects: workbooks with workbook, workbook has worksheets with worksheet
xlApp = New Excel.Application
xlBooks = xlApp.Workbooks
xlBook = xlBooks.Add
xlBook.SaveAs(ExcelFileName)
xlSheet = xlBook.Worksheets(1)

' uitzetten na testen
xlApp.Visible = True

intAantalExcelSheets = 0

If Not OpenConnectionMasterParts() Then
MsgLabel.Text = "Due to technical problems this site is temporarily not available"
Exit Sub
End If

' doorlezen alle trucks
For Each chkItem In CheckBoxListTruck.Items
If chkItem.Selected = True Then
generateTruck()
End If
Next
xlBook.Save()
Do While Not xlBook.Saved
System.DayOfWeek.Friday.GetHashCode()
Loop
MsgLabel.Text = "Truck file created, press download button"
ButtonDownload.Enabled = True
conMasterParts.Close()

Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
xlSheet = Nothing
xlBook.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
xlBook = Nothing
xlBooks.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks)
xlBooks = Nothing
xlApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlApp = Nothing
Catch
' Do nothing
End Try

Catch exception As Exception
MsgLabel.Text = exception.ToString()
Finally
' Invoke garbage collector before termination
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
End Try

End Sub

Public Sub generateTruck()

Try
intAantalExcelSheets = intAantalExcelSheets + 1
' naar volgende sheet of nieuwe sheet achteraan toevoegen
If intAantalExcelSheets > 3 Then
xlSheet = xlBook.Sheets.Add(, xlApp.Worksheets(xlApp.Worksheets.Count))
Else
xlSheet = xlBook.Sheets.Item(intAantalExcelSheets)
End If

xlSheet.Activate()
xlSheet.Name = Left(chkItem.Text, 4)

xlSheet.Range("A3").Value = "Truck"
xlSheet.Range("B3").Value = "Truck description"
xlSheet.Range("C3").Value = "Truck brand"
xlSheet.Range("D3").Value = "PM Section"
xlSheet.Range("E3").Value = "PM SubSection"
xlSheet.Range("F3").Value = "PartNr"
xlSheet.Range("G3").Value = "Part description"
xlSheet.Range("H3").Value = "Part l.description"
xlSheet.Range("I3").Value = "Quantity"
xlSheet.Range("J3").Value = "Snr First"
xlSheet.Range("K3").Value = "Snr Last"
xlSheet.Range("L3").Value = "Service hours"
xlSheet.Range("M3").Value = "Comments"
xlSheet.Range("N3").Value = "Group"
xlSheet.Range("O3").Value = "Group description"
xlSheet.Range("P3").Value = "Diesel"
xlSheet.Range("Q3").Value = "LPG"
xlSheet.Range("R3").Value = "Electric"
With xlSheet.Range("A3", "R3").Font
.Name = "Arial"
.Size = 10
.Bold = True
.Color = RGB(255, 0, 0)
End With
xlSheet.EnableAutoFilter = True

'HERE IT GOES WRONG:
xlSheet.Range("A3", "R3").AutoFilter


strQry = "SELECT dbo.Truck.TruckCode, " _
& "dbo.Truck.TruckDescription," _
& "dbo.Truck.TruckBrand," _
& "dbo.GroupingPart.PMSection," _
& "dbo.GroupingPart.PMSubsection," _
& "dbo.GroupingPart.PartNr," _
& "dbo.Part.PartDescription," _ & "dbo.GroupingPart.Appl_Description," _
& "dbo.GroupingPart.Qty," _
& "dbo.GroupingPart.SnrFirst," _
& "dbo.GroupingPart.SnrLast," _
& "dbo.GroupingPart.Service_Hours," _
& "dbo.GroupingPart.Comments," _
& "dbo.Grouping.GroupCode," _
& "dbo.Grouping.GroupDescription," _
& "dbo.GroupingPart.Engine_Diesel," _
& "dbo.GroupingPart.Engine_Lpg," _
& "dbo.GroupingPart.Engine_Electric " _
& "FROM (dbo.Grouping INNER JOIN (dbo.Truck INNER JOIN dbo.TruckGrouping ON dbo.Truck.TruckCode = dbo.TruckGrouping.TruckCode) ON dbo.Grouping.GroupCode = dbo.TruckGrouping.GroupCode) INNER JOIN (dbo.Part INNER JOIN dbo.GroupingPart ON dbo.part.PartNr = dbo.GroupingPart.PartNr) ON dbo.Grouping.GroupCode = dbo.GroupingPart.GroupCode " _
& "WHERE ((dbo.Truck.TruckCode) = '" & Left(chkItem.Text, 4) & "' AND (Left(dbo.GroupingPart.PartNr,1) = ' ')) " _
& "ORDER BY dbo.GroupingPart.PMSection ASC, dbo.GroupingPart.PMSubsection ASC, dbo.GroupingPart.PartNr ASC, dbo.GroupingPart.SnrFirst ASC, dbo.GroupingPart.SnrLast ASC "

ExecuteQuery(strQry)
xlSheet.Range("A4", "R4").CopyFromRecordset(rsMasterParts)

Catch exception As Exception
MsgLabel.Text = exception.ToString()
Finally
End Try

End Sub