Visual Basic and Excel

[jaytee81]

Hi there

I'm working on a VB project where I'm extracting data from an excel spreadsheet. My problem is that the way the excel spreadsheet has been laid out is making it difficult for me to get at this data particularly as my skills with excel access is minimal. Anyway, the first 3 rows of the spreadsheet constitute a header with a title etc in them. Then the spreadsheet is set out so that it hides rows, for example the first visible row to me after Row 3 of the header is 302 therefore rows 4 to 301 have been hidden. Now, the project only requires me to extract the information from these visible rows, however, the spreadsheet is to be updated regularly so this means the first visible row will change with each update that means I cannot hardcode any row numbers into VB. So what I need is code that can find the first visible row number of a spreadsheet and tell me the last row with some data in it. Any help with this would be greatly appreciated. As I say my Excel handling skills are limited hence the struggle with how to get this data. Thanks in advance for your time.

 

[HarleyQuinn]

I'm assumin that you know how to connect to Excel and specify the workbook/worksheet you are working on.

Once you have that you could use something along the lines of:

Dim i As Integer

i = 4

Do Until Trim(Range("A" & i).Value) = ""

If Range("A" & i).EntireRow.Hidden = False Then
Range("B" & i).Value = Range("A" & i).Value
End If
i = i + 1
Loop

That code will loop through the first column of the Excel sheet (starting at row 4) until it encounters a cell that's value is "" (basically blank). Alon gthe loop if it encounters a cell that's not hidden it will write the value for the cell in the corresponding cell in the second column.

That should get you started.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jaytee81]

Hi HarlyQuinn, yeah I can do all the connection stuff and specify workbooks etc. Just couldn't figure out what to do to pick out this first visible row. I will try your suggestion and get back with my results.

Thanks

 

[HarleyQuinn]

A useful tip to remember when working with Excel is to record a macro in Excel of yourself doing what you want to do in VB (or in this case something similar). It will allow you to see the relevant properties/methods used in Excel and then you can use that knowledge to code something that will meet your requirements.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[jaytee81]

Hi HarlyQuinn

With a few changes to your code I have got what I'm after. However, in the excel file there are four different worksheets that I need to access and even though I'm changing the active worksheet in my code the function to get the first row still only seems to bring back the row number for the first sheet. Any ideas. Thanks again for your help.

 

[HarleyQuinn]

Would you be able to show us the code you have so far so we can have a look at it please?

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[RoyVidar]

Do you prefix the range object with your sheet object variable (or the active sheet)?

[tt]set sh = wr.sheets("SheetN")
Do Until Trim(sh.Range("A" & i).Value) = ""

' or

Do Until Trim(wr.ActiveSheet.Range("A" & i).Value) = ""[/tt]

Be careful with the references, and qualify all of the Activethingies, cells... with the relevant excel objects or object variables, else you'll very likely end up with automation errors.

Roy-Vidar

 

[jaytee81]

Thanks Roy

Used the activesheet in front of Range and it now works. I can now continue with the rest of the project. Again thanks to HarleyQuinn for your advice also.

 

[HarleyQuinn]

Glad to help

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.