By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,704 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,704 IT Pros & Developers. It's quick & easy.

Excel problem--object showing gettype insead of more options

P: n/a
..Cells(1,y).GetType ownly shows instead of .Value, .Copy, etc.
The code below is derived from samples, it should work, but I've messed up
somewhere...

Imports System.Windows.Forms
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports MSForms = Microsoft.Vbe.Interop.Forms

' Office integration attribute. Identifies the startup class for the
workbook. Do not modify.
<Assembly: System.ComponentModel.DescriptionAttribute("Office StartupClass,
Version=1.0, Class=FuelConsumption.OfficeCodeBehind")>

Public Class OfficeCodeBehind

Friend WithEvents ThisWorkbook As Excel.Workbook
Friend WithEvents ThisApplication As Excel.Application

#Region "Generated initialization code"

' Default constructor.
Public Sub New()
End Sub

' Required procedure. Do not modify.
Public Sub _Startup(ByVal application As Object, ByVal workbook As Object)
ThisApplication = CType(application, Excel.Application)
ThisWorkbook = CType(workbook, Excel.Workbook)
End Sub

' Required procedure. Do not modify.
Public Sub _Shutdown()
ThisApplication = Nothing
ThisWorkbook = Nothing
End Sub

' Returns the control with the specified name on ThisWorkbook's active
worksheet.
Overloads Function FindControl(ByVal name As String) As Object
Return FindControl(name, CType(ThisWorkbook.ActiveSheet,
Excel.Worksheet))
End Function

' Returns the control with the specified name on the specified worksheet.
Overloads Function FindControl(ByVal name As String, ByVal sheet As
Excel.Worksheet) As Object
Dim theObject As Excel.OLEObject
Try
theObject = CType(sheet.OLEObjects(name), Excel.OLEObject)
Return theObject.Object
Catch Ex As Exception
' Returns Nothing if the control is not found.
End Try
Return Nothing
End Function
#End Region

' Called when the workbook is opened.
Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
CheckForVoyageNumber()
End Sub

Private Sub ThisWorkbook_BeforeClose(ByRef Cancel As Boolean) Handles
ThisWorkbook.BeforeClose
Cancel = False
End Sub
Sub CheckForVoyageNumber()

ThisWorkbook = ThisApplication.Workbook.add
Dim sheetData As New Excel.Worksheet
Dim sheetFuelVoyage As Excel.Worksheet =
ThisWorkbook.Worksheets("Fuel-Voyage")
sheetData = ThisWorkbook.Worksheets("Data")
Try
Dim myRowcount, x, y As Integer
y = 1
'column 1 is datetime data
Do While sheetData.Cells(1, y).value <> Convert.DBNull
'count number of rows where VoyageNumber(x=4) is not empty
If sheetData.Cells.Item(4, y) <> Convert.DBNull Then
myRowcount = myRowcount + 1
MsgBox("test3: " & myRowcount & "y: " & y)
'for each row, copy x=4, x=9, x=11, x=13, x=14, x=16 to
Worksheets("Fuel-Voyage").Cells( , )
sheetData.Cells(4, y).Copy(sheetFuelVoyage.Cells(2, y +
1))
sheetData.Cells(9, y).Copy(sheetFuelVoyage.Cells(3, y +
1))
sheetData.Cells(11, y).Copy(sheetFuelVoyage.Cells(4, y +
1))
sheetData.Cells(13, y).Copy(sheetFuelVoyage.Cells(5, y +
1))
sheetData.Cells(14, y).Copy(sheetFuelVoyage.Cells(6, y +
1))
sheetData.Cells(16, y).Copy(sheetFuelVoyage.Cells(7, y +
1))
End If
y = y + 1 'incr row count
Loop

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

End Class

Nov 21 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
got it working, found sample that worked.--clunky tho..

Sub CheckForVoyageNumber()

Dim sheetData, sheetFuelVoyage, sheetSummary As Excel.Worksheet
sheetData = CType(Me.ThisApplication.Sheets.Item("Data"),
Excel.Worksheet)
sheetFuelVoyage =
CType(Me.ThisApplication.Sheets.Item("Fuel-Voyage"), Excel.Worksheet)
sheetSummary = CType(Me.ThisApplication.Sheets.Item("Summary"),
Excel.Worksheet)
Try
Dim myFuelrows, x, y As Integer
x = 2
myFuelrows = 0
'column 1 is datetime data
Do While Convert.ToString(CType(sheetData.Cells(x, 1),
Excel.Range).Value) <> ""

'count number of rows where VoyageNumber(x=4) is not empty
If Convert.ToString(CType(sheetData.Cells(x, 4),
Excel.Range).Value) <> "" Then
myFuelrows = myFuelrows + 1
'MsgBox("test3: " & "x: " & x & CType(sheetData.Cells(x,
4), Excel.Range).Value)
'for each row, copy x=4, x=8, x=10, x=13, x=14, x=16 to
Worksheets("Fuel-Voyage").Cells( , )
CType(sheetData.Cells(x, 4),
Excel.Range).Copy(sheetFuelVoyage.Cells(myFuelrows + 1, 2))
CType(sheetData.Cells(x, 8),
Excel.Range).Copy(sheetFuelVoyage.Cells(myFuelrows + 1, 3))
CType(sheetData.Cells(x, 11),
Excel.Range).Copy(sheetFuelVoyage.Cells(myFuelrows + 1, 4))
CType(sheetData.Cells(x, 12),
Excel.Range).Copy(sheetFuelVoyage.Cells(myFuelrows + 1, 5))
CType(sheetData.Cells(x, 13),
Excel.Range).Copy(sheetFuelVoyage.Cells(myFuelrows + 1, 6))
CType(sheetData.Cells(x, 14),
Excel.Range).Copy(sheetFuelVoyage.Cells(myFuelrows + 1, 7))
CType(sheetData.Cells(x, 16),
Excel.Range).Copy(sheetFuelVoyage.Cells(myFuelrows + 1, 8))

CType(sheetData.Cells(x, 4),
Excel.Range).Copy(sheetSummary.Cells(myFuelrows + 21, 1))
CType(sheetData.Cells(x, 8),
Excel.Range).Copy(sheetSummary.Cells(myFuelrows + 21, 2))
CType(sheetData.Cells(x, 11),
Excel.Range).Copy(sheetSummary.Cells(myFuelrows + 21, 3))
CType(sheetData.Cells(x, 12),
Excel.Range).Copy(sheetSummary.Cells(myFuelrows + 21, 4))
CType(sheetData.Cells(x, 13),
Excel.Range).Copy(sheetSummary.Cells(myFuelrows + 21, 5))
CType(sheetData.Cells(x, 14),
Excel.Range).Copy(sheetSummary.Cells(myFuelrows + 21, 6))
CType(sheetData.Cells(x, 16),
Excel.Range).Copy(sheetSummary.Cells(myFuelrows + 21, 7))
End If
x = x + 1 'incr row count
Loop

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

Nov 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.