Excel and VB

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

hi,

I have an excel sheet, and i want to be able to read thru' the cells of the excel sheet and pick up values and store them in a place for every line.


like read all B columns till the end of the file and then read all D columns. I want to be able to store these two values in someplace, so then i could do some other calculations.

please let me know whether i am going on the right track so far and just need some help to figure out how to read the xls sheet.

here is my code so far,
' appExcel is set as an Object
Dim appExcel As Object

' Specify path for GetObject function
On Error GoTo fix_err
' XLS File is loaded here
Set appExcel = GetObject("C:\temp\QReplacementData.xls")
' The Application remains visible
appExcel.Application.Visible = True
' The Excel file is hidden but running
appExcel.Parent.Windows(1).Visible = False

' want to read column B and D. and store those values

' The appExcel is set to Nothing
Set appExcel = Nothing

' Appropriate Message is displayed
MsgBox "Process Com"
fix_err:
Debug.Print Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear

I greatly appreciate any help/suggestions.
 
Hi there,

Do you know VBA, Sean? To manipulate Excel spreadsheets, you are
effectively using the Excel Object Model that VBA works with. If you can write
your progam within Excel using VBA then it is pretty straightforward to
translate it into whatever flavour of VB you are using.

I would recommend getting a book on VBA - one that goes into the Object
Models of Excel. Also, in Excel, you can record macros while you do certain
operations and then go into the Macro Explorer to see what the equivalent code
is.

The problem with answering your question is that it is very vague. You
don't define what "to the end of the file" is for column B and D. The end of
the column may be the last cell with a value, but if empty cells are allowed
in the column, what then?

You want to "store these two values someplace". The result of extracting a
column of cells will be an array. Are you expecting strings, numbers, dates?
What do you want to do with them?

The code that you have given looks reasonable enough, but you need to be
aware that Excel and Excel objects are COM-based and require releasing. In
..NET we use Runtime.InteropServices.Marshal's ReleaseComObject method. I don't
know what you'll have to use.

DF
 
Back
Top