467,076 Members | 996 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,076 developers. It's quick & easy.

Excel problem--object showing gettype insead of more options

..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
  • viewed: 1947
Share:
1 Reply
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.

Similar topics

4 posts views Thread by Lord2702 | last post: by
4 posts views Thread by nuhura01@yahoo.com | last post: by
1 post views Thread by irfan.mohammed@gmail.com | last post: by
reply views Thread by Gordon.E.Anderson@gmail.com | last post: by
13 posts views Thread by cartoper@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.