Read headers from Excel Worksheet (1 Viewer)

KRISTIRG

Registered User
Joined
Jan 13, 2020
Messages
10
Okay, below is the final working code. Thank you guys so much for the help!
For anyone else using this in the future, make sure to set the value of the combobox to Null before this code runs. And set the column width to more than the 0" default so you can see it! Lol
The only downside is that this code as well as the code that gives the list of sheet names are both INCREDIBLY slow. Downright painfully. I have no idea why, it doesn't have much to read through.
Code:
Public Function ReadHeaderNames()
Dim objExc As Object
Dim objWbk As Object
Dim objWsh As Object
Dim Rng As Object
Dim col As Integer
Dim LastCol As Integer
Dim row As Integer
Dim i As Variant
Set objExc = CreateObject("Excel.Application")
Set objWbk = objExc.Workbooks.Open(ScriptFile_box.Value)
Set objWsh = objWbk.Sheets(SelectScriptWorksheet_cmbo.Value)
Set Rng = objWsh.Range("A1", objWsh.Range("A1").End(2))
For Each i In Rng
    SelectFieldHeader_cmbo.AddItem i.Value
Next i

objWbk.Close
Set objWsh = Nothing
Set objWbk = Nothing
Set objExc = Nothing
End Function
This also has me curious now if I couldn't make it a 2 column combobox and have it fill with "A | Name" "B | Social" etc. So they could see the column letter as well as the name. Sometimes the names the system gives fields are pretty ambiguous, may be helpful to have both. But I feel that may be a battle for another day!
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,631
Hi. Glad to hear you got it sorted out. Not sure if it's worth it, but you might consider storing all that information in a temporary table, so it's only slow once. For instance, after the user selects an Excel file, you could read all the worksheet names and headers into a temp table. You can then use this table for your comboboxes. Just a thought...
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
This also has me curious now if I couldn't make it a 2 column combobox and have it fill with "A | Name" "B | Social" etc. So they could see the column letter as well as the name. Sometimes the names the system gives fields are pretty ambiguous, may be helpful to have both. But I feel that may be a battle for another day!
Would you have more than 26?
If not use the ASC() and CHR() functions starting the loop at 65. ?

HTH
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,016
This will give you the column name from the code I supplied you

Code:
Split(i.Address, "$")(1)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom