..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 1 2213
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Lord2702 |
last post by:
Fri. Aug. 20, 2004 10:50 AM PT
I want to create an Object/ Class from its name as string. I will explain
it....
Lets say I have a class BaseClass, and from this class I derived 5 classes...
|
by: nuhura01 |
last post by:
Hi,
I want to create an 'open file button' using asp control button, which
will display open file
dialog box for users to choose the location of folders to be read. It
might be easy for Windows...
|
by: irfan.mohammed |
last post by:
Dear All,
I need your help,i am using ftp_get for getting the file from remote
machine.now i am able to save only at one location.i want the user to
select the directory any where in the...
|
by: Gordon.E.Anderson |
last post by:
short description:
i've got a .net web site set up using a tableadapter attached to a
sql
server table - returning results only, no update of data. i've got a
query (qry code below) set up to...
|
by: cartoper |
last post by:
I am working on writing an Apache Module (in C/C++) that needs to
resize (down) large images (4MB to 16MB) VERY quickly. The objective
is not to make the images look great for printing, just look...
|
by: pixie |
last post by:
Hi,
I have some code that I have been using for more than a year. Excel VBA. Today it stopped working. I have no idea why. Here is the code: Range ("E1").Select
If IsEmpty(ActiveCell) Then...
|
by: sukhsinghin |
last post by:
Hi,
I am trying to make a copy of excel file using file.copy(path1,path2).
its copying but making a blank file. Data is not getting copied.
File.Copy(path, path2)
Can anyone help me on...
|
by: MyWaterloo |
last post by:
Hey all,
I don't know where to go with this Excel question so I thought I would come here. You all have always been able to answer/help me with all my Access questions. I know this is not...
|
by: BaseballGraphs |
last post by:
Hello,
I was hoping someone could provide or suggest a resource where I may be able to see implementation of the following programming situation.
I would like to create a simple radio button...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |