Read headers from Excel Worksheet (1 Viewer)

[KRISTIRG]

I have a fairly simple form with several comboboxes to allow users to tell me specifically what records they need to have updated. (Small part of a ticketing system)
First there is a FileDialog button so they can select their Excel file.
Then the next combo is populated by just reading each of the worksheet names in that file.
Now I need one that will be a list of all the headers (Row1) on that selected worksheet.
Here is what I did for the worksheets, it's very simple. I'd like to do something along these lines for the column headers but I can't seem to figure it out.
Honestly I'm having a hard time even coming up with a starting point though that could just be a Monday morning issue!
Appreciate any help!

Public Function ReadSheetNames()
Dim objExc As Object
Dim objWbk As Object
Dim objWsh As Object
Dim i As Long
Me.SelectScriptWorksheet_cmbo.Clear
Set objExc = CreateObject("Excel.Application")
Set objWbk = objExc.Workbooks.Open(ScriptFile_box)
For Each objWsh In objWbk.Sheets
    SelectScriptWorksheet_cmbo.AddItem objWsh.Name
Next

Set objWsh = Nothing
objWbk.Close
Set objWbk = Nothing
objExc.Quit
Set objExc = Nothing

    
End Function

 

[theDBguy]

Hi. Welcome to AWF!


Just a shot in the dark, but maybe something like:

[B]Dim x As Long
x = 1[/B]

 ...
 For Each objWsh In objWbk.Sheets
     SelectScriptWorksheet_cmbo.AddItem objWsh.Name
     [B]Do While objWsh.Cells(1,x)<>""
[/B][B][B]        HeaderListCombo.AddItem objWsh.Cells(1,x).Value
[/B]        HeaderListCombo.AddItem objWsh.Name
       x = x + 1
     Loop[/B]
  Next
...

Hope that helps...

 

[KRISTIRG]

It looks like that is going through all the worksheets instead of just the one that was selected in the previous dropdown?
It gives me an undefined error on Cells, I have to use late binding.
And wouldn't adding both the name and the value of the cell give me 2 columns? Like "A1 Imports" "B1 ScriptInfo" etc.
I tried to F8 through this but there were just too many errors.

 

[theDBguy]

It looks like that is going through all the worksheets instead of just the one that was selected in the previous dropdown?
It gives me an undefined error on Cells, I have to use late binding.
And wouldn't adding both the name and the value of the cell give me 2 columns? Like "A1 Imports" "B1 ScriptInfo" etc.
I tried to F8 through this but there were just too many errors.

Hi. I was thinking you want both the name of the sheet plus the headers, so you can filter the combo based on the name selected from the other combo (cascading combos). Another approach you could try is only populate the header dropdown in the AfterUpdate event of the sheet name combo, using the code I gave you for populating the header combo (just modify it to specify the sheet name to get). Hope that helps...

 

[KRISTIRG]

I have to wait until the user picks a worksheet before I will know what headers to populate.
So I do have it on the after update event of the Worksheet selection box.
This is really messy, just playing with ideas and testing, hence all the commented out rows.
I think it's a decent direction but without being able to declare a range I have no idea how to go about it.

Dim objExc As Object
Dim objWbk As Object
Dim objWsh As Object
Dim col As Integer
Dim LastCol As Integer
Dim row As Integer
'Me.SelectFieldHeader_cmbo.Clear
Set objExc = CreateObject("Excel.Application")
Set objWbk = objExc.Workbooks.Open(ScriptFile_box.Value)
Set objWsh = objWbk.Sheets(SelectScriptWorksheet_cmbo.Value)
 
'Set Rng = objWsh("A" & i).End(xlToRight).Column
'LastCol = objWsh.Rng("A" & i).End(xlToRight).Column
'For Each i In Rng
    '
    'SelectFieldHeader_cmbo.AddItem cell.Value
    
'Next i

End Function

 

[theDBguy]

Hmm, what happened with using Cells()? Do you really need to use a Range()?

 

[KRISTIRG]

The code you gave might work if I could use the reference Cells.

 

[KRISTIRG]

Object defined error. As I said, I have to use late binding so I can't use Cells, Range, etc.
All I need is all of the values in Row 1 until there is nothing left in it.

 

[theDBguy]

Object defined error. As I said, I have to use late binding so I can't use Cells, Range, etc.
All I need is all of the values in Row 1 until there is nothing left in it.

Hmm, I've used late binding before but still was able to use Cells() with it. Granted, I can't do any testing right now, but I'm just wondering why it mattered if you're using late binding this time? Just thinking out loud...

 

[KRISTIRG]

I'm not sure what else would cause an "Application-defined or object-defined error" on any line that has Cells in it. With late binding I can't use Range, Workbook, Worksheet, or anything else that would be defined in the Excel library, so I'm guessing Cells falls into that list too.

 

[theDBguy]

Okay. I may have remembered it wrong. See if this previous thread has something you can use.


Also, this link might come in handy as well.


Cheers!

 

[Micron]

I see a couple of problems. One is that you can't use For Each on a non-object loop. Regardless of late binding or not, you can refer to properties of an object you have declared, so Cell and Range isn't out of the question. Using this with your code
?objwsh.range ("A1").value
I get
DATE
as a result (with my spreadsheet). I cannot use Cell(s) in that context however, because it's not a property or member of the sheet, it's related to the range object if I'm not mistaken. I'm not real adept at Excel automation but when I need help I usually figure it out and here's where I usually start...
http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

 

[KRISTIRG]

Thank you for the tip on using the For Each! Hadn't thought about that was just looking at what I had just used.
Although I can't even seem to get to that point in the code anyway. Lol

I tried just the below little sliver of code and I get object doesn't support this property or method on the line that has Range in it.

Dim objExc As Object
Dim objWbk As Object
Dim objWsh As Object
Dim col As Integer
Dim LastCol As Integer
Dim row As Integer
Set objExc = CreateObject("Excel.Application")
Set objWbk = objExc.Workbooks.Open(ScriptFile_box.Value)
Set objWsh = objWbk.Sheets(SelectScriptWorksheet_cmbo.Value)
 
objWsh.Range("A1").Value
Debug.Print

 

[theDBguy]

Thank you for the tip on using the For Each! Hadn't thought about that was just looking at what I had just used.
Although I can't even seem to get to that point in the code anyway. Lol

I tried just the below little sliver of code and I get object doesn't support this property or method on the line that has Range in it.

Dim objExc As Object
Dim objWbk As Object
Dim objWsh As Object
Dim col As Integer
Dim LastCol As Integer
Dim row As Integer
Set objExc = CreateObject("Excel.Application")
Set objWbk = objExc.Workbooks.Open(ScriptFile_box.Value)
Set objWsh = objWbk.Sheets(SelectScriptWorksheet_cmbo.Value)
 
objWsh.Range("A1").Value
Debug.Print

Hi. What do you get if you changed the following:

objWsh.Range("A1").Value
Debug.Print

Into this?

Debug.Print objWsh.Range("A1").Value

 

[Gasman]

You need to set that range value to something?
I've just tried your code and used what theDBguy has just suggested and it works fine?

 

[KRISTIRG]

Okay so this...…
Debug.Print objWsh.Range("A1").Value
DOES WORK!!! Yay!! Lol. 1 step out of the way.
So now how do I get it to read all the way across row 1 until there isn't anything left?

I felt like I could do something with the line "Set Rng = objWsh("A" & I).End(xlToRight).Column" but no luck so far.

 

[theDBguy]

Okay so this...…
Debug.Print objWsh.Range("A1").Value
DOES WORK!!! Yay!! Lol. 1 step out of the way.
So now how do I get it to read all the way across row 1 until there isn't anything left?

I felt like I could do something with the line "Set Rng = objWsh("A" & I).End(xlToRight).Column" but no luck so far.

Hi. Did you check out the second link I gave you earlier? I was thinking you could find out the "last column" on the first row and construct your Range that way. Either that or you could try using the loop I originally gave you and simply walk down all the columns on the first row until you find an empty one.

 

[KRISTIRG]

That's the question though.....how do I set the range? I need all the headers, it will always start at A1 but every file will have different numbers of columns. So the range needs to be dynamic and return the value of every cell in row 1 until it's empty.

 

[Gasman]

This will work with latebinding?
Amend to suit.

HTH

Sub test_LB()
Dim objExc As Object
Dim objWbk As Object
Dim objWsh As Object
Dim i As Object, Rng As Object
Dim col As Integer
Dim LastCol As Integer
Dim row As Integer
Set objExc = CreateObject("Excel.Application")
Set objWbk = objExc.Workbooks.Open("C:\Temp\VBAUploadTest.xls")
Set objWsh = objWbk.Sheets(3)

Set Rng = objWsh.Range("A1", objWsh.Range("A1").End(2))
For Each i In Rng
    '
    'SelectFieldHeader_cmbo.AddItem cell.Value
    Debug.Print i.Value
Next i

objWbk.Close
Set objWsh = Nothing
Set objWbk = Nothing
Set objExc = Nothing
End Sub

 

[theDBguy]

That's the question though.....how do I set the range? I need all the headers, it will always start at A1 but every file will have different numbers of columns. So the range needs to be dynamic and return the value of every cell in row 1 until it's empty.

Hi. Was this question directed at me? If so, I was saying the link I gave you earlier will show you how to dynamically get the value for your range. It has a code snippet to help you find out how many columns the selected worksheet has. Have you looked at it and tried it? If so and it doesn't work, can you tell us how you tried it and what actually happened. Also, as I mentioned, you could do it manually yourself using a loop. For example:

x = 1
With objWsh
   Do While .Range("A" & x)<>""
      'get the header value
      x = x + 1
   Loop
End With
...

(untested)