Getting Data from Excel to VB

[Greedo]

I am trying to get data from a spreadsheet into a VB file for a macro. There aren`t any manuals here in English so although I`m sure this is an easy question I have no way of looking it up. Anyway, I think I can do this using automation. I only need to get about 10 cells worth of data into the macro. Once its there I think I can do the rest. If someone can get me some sample code of getting some cells of data from excel to VB I think I can figure out what I need to do. Anyway, any help would be appreciated.

 

[StewartJ]

objExcel = CREATEOBJECT("Excel.Application&quot
With objExcel.Worksheets(1).UsedRange
For intRowNo = 1 TO .Rows.Count
varCellValue = .Cells(intRowNo, intColNo).Value
Next
End With
objExcel.Quit
Set objExcel = Nothing

 

[Greedo]

Alright, so I`m dumber than I thought. I do need a bit more help. Let`s assume the file name is Greedo.xls and the worksheet is called Greedois(retarded).

Also, later on I will need to get some more data out of a different worksheet in the same file. Do I need to do anything differently because of this?

 

[Nikita6003]

Hi Greedo,

Try something like this (I'm assuming you've got 2 columns of data & an unknown number of rows ... I'm also assuming Greedo.xls is in Excel's Current directory - it's safer to specify path as well)

Sub Test()
Dim l_objXL As Excel.Application
Dim l_wkbGreedo As Excel.Workbook '(you can leave off the Excel. if u like)
Dim l_wksGreedoIs As Excel.Worksheet

Dim l_vArray() As Variant
Dim l_lRow As Long

'Create App,Book,Sheet objects
Set l_objXL = CreateObject("Excel.Application&quot
Set l_wkbGreedo = l_objXL.OpenWorkBook("Greedo.xls&quot
Set l_wksGreedoIs = l_wkbGreedo.Sheets("GreedoIs(retarded)&quot

'Set array dimensions equal to the number of ros in your worksheet / 2 columns
ReDim l_vArray(l_wksGreedoIs.UsedRange.Rows.Count - 1, 1)

'Read cells into array
With l_wksGreedoIs
For l_lRow = 1 To l_wksGreedoIs.UsedRange.Rows.Count
l_vArray(l_lRow - 1, 0) = l_wksGreedoIs.Cells(l_lRow, 1)
l_vArray(l_lRow - 1, 1) = l_wksGreedoIs.Cells(l_lRow, 2)
Next l_lRow
End With

l_wkbGreedo.Close
l_objXL.Quit

'Add code to do whatever u like to your array data
End Sub


If you need to open another workbook & read the data just copy/paste & adjust your code

Hope it helps

Cheers
Nikki

 

[Nikita6003]

Greedo:

In my prev. post PLEASE REPLACE LINE
Set l_wkbGreedo = l_objXL.OpenWorkBook("Greedo.xls&quot
WITH
Set l_wkbGreedo = l_objXL.Workbooks.Open("Greedo.xls&quot

(sorry - must be half asleep here ...)

Cheers
Nikki

 

[StewartJ]

Sorry if I confused you - forgot to open the worksheet in my code!!

 

[Greedo]

Well that is an unsettling though Stewart because I thought I understood your submission pretty well. Nikita- let me look at yours again and see if I can follow it.

 

[Greedo]

OK, you guys really are helping me quite a bit. I haven't programmed anything for about three years and back then it was in C. Anyway, enough of me, more about my problem. Here is the code I have in there right now. I'm calculating heat flow into a car. Since the sun shines in a specific direction I need to have the window dimensions related to what direction the car is going. So the windim is the window area and windim(1,1) is the area of the window facing the 1 direction when the car is moving in the 1 direction. Does that make sense? In this case its the front window when facing north. windim(1,2) is the right window, or east facing window when traveling north. I hope that makes sense. Later on the program calculates what the heat flow is for each direction and figures out which direction the car is facing for a maximum load (and then what that load is).






objExcel = CreateObject("Excel.Application&quot
With objExcel.Worksheets(1).UsedRange
For Direction = 1 To 4
windim(Direction, Direction) = .Cells(20, 2).Value
windim(Direction, Direction + 1) = .Cells(18, 2).Value
windim(Direction, Direction + 2) = .Cells(20, 2).Value
windim(Direction, Direction + 3) = .Cells(18, 2).Value

Next
End With
objExcel.Quit
Set objExcel = Nothing




'this puts the solar load intensity data in, relating it to direction


For Direction = 1 To 4
ColumnNum = Direction + 1
Worksheets("Solar Intensity Data&quot.Select
With Worksheets("Solar Intensity Data&quot.Range("A1&quot
SolarLoadIntensity(Direction) = .Offset(RowNum, ColumnNum).Value
End With
Next Direction

 

[Greedo]

I forgot to say what SolarLoadIntensity is although it should be somewhat self explanatory. This variable is dependent on the direction so that SolarLoadIntensity(1) is used with windim(1,1 ) to calculate the solar load coming in the front window when traveling north.

Also, I won't need to open the excel file as it is all done inside the excel file. If this didn't rely on data coming from more than one sheet I would just do all of it in one spreadsheet.

 

[Greedo]

OK. This is what I have (some old code was just 'ed and the dimming isn't included but I think you can figure out what this is. Its still not working though. It stops between the Set wkb line adn the Set wks line. Any suggestions?




' Create App,Book,Sheet objects
Set objXL = CreateObject("Excel.Application&quot
Set wkb = objXL.Workbooks.Open("Tony KTMB Load Calculation ALSTOM.xls&quot
Set wks = wkb.Sheets("Spec(Cooling)&quot



'this connects the window dimension to the side it faces for the direction of travel

'Worksheets("Spec(Cooling)&quot.Select

'With Worksheets("Spec(Cooling)&quot
With wks
For Direction = 1 To 4
windim(Direction, Direction) = wks.Cells(20, 2).Value
windim(Direction, Direction + 1) = wks.Cells(18, 2).Value
windim(Direction, Direction + 2) = wks.Cells(20, 2).Value
windim(Direction, Direction + 3) = wks.Cells(18, 2).Value
Next Direction
End With



'this puts the solar load intensity data in, relating it to direction



Set wks = wkb.Sheets("Solar Intensity Data&quot
With wks

'Worksheets("Solar Intensity Data&quot.Select

'With Worksheets("Solar Load Intensity Data&quot
For Direction = 1 To 4
ColumnNum = Direction + 2
SolarLoadIntensity(Direction) = wks.Cells(RowNum, ColumnNum).Value

Next Direction
End With