..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
