G
Guest
..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("OfficeStartupClass,
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
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("OfficeStartupClass,
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