Viewing Excel field names with VBA 2

[dpimental]

How do I view the "field names" of an Excel Spreadsheet, and then write them to an access table.

And I don't want to import the Excel Document. David Pimental
(US, Oh)
dpimental@checkfree.com

 

[Nikita6003]

could you specify what you mean by field names?

Thanks
Nikki

 

[dpimental]

You know how (usually) the first row of a spreadsheet is for the names of the fields, and then each row after that is the values of those fields.

How can I get the field names only? David Pimental
(US, Oh)
dpimental@checkfree.com

 

[Nikita6003]

Try this:

Sub GetFieldNamesFromXL()
    Dim l_appXL As Excel.Application
    Dim l_wkbSheetNames As Workbook
    Dim l_wksSheet As Worksheet
    Dim l_iColumn As Integer
    
    Dim l_rsRecordset As Recordset
    
    Set l_appXL = CreateObject("Excel.Application")
    l_appXL.Visible = True
    
    Set l_wkbSheetNames = l_appXL.Workbooks.Open("C:\Temp\Test.xls")
    Set l_wksSheet = l_wkbSheetNames.Sheets(1)
    Set l_rsRecordset = CurrentDb.OpenRecordset("tblFieldname", dbOpenTable, dbOpenDynamic)
    
    Do Until l_wksSheet.Cells(1, l_iColumn) = ""
        l_rsRecordset.AddNew
        l_rsRecordset.Fields("FieldName") = l_wksSheet.Cells(1, l_iColumn).Text
        l_rsRecordset.Update
        l_iColumn = l_iColumn + 1
    Loop
    
    'Release xl objects
    Set l_rsRecordset = Nothing
    Set l_wksSheet = Nothing
    l_wkbSheetNames.Close xlDoNotSaveChanges
    Set l_wkbSheetNames = Nothing
    l_appXL.Quit
    Set l_appXL = Nothing
    
End Sub

That'll pick up the values of all cells one by one in row 1 until the cell is empty


HTH - & is what u mean!
BTW - it assumes that:
a. you've set a reference to xl in your database
b. the field names are in row 1

Cheers
Nikki

 

[dpimental]

Groovy Baby!

I'll try it. David Pimental
(US, Oh)
dpimental@checkfree.com

 

[dpimental]

Nikki, any way to get the field type/format (number, date, text, memo, ect) from the field following the name? David Pimental
(US, Oh)
dpimental@checkfree.com

 

[dpimental]

Nikki, what references of "x1" need to be set in the database. I've set the MS Excel 9.0 Object Library.

What else do I need to set. I keep getting various errors. David Pimental
(US, Oh)
dpimental@checkfree.com

 

[SBendBuckeye]

Thanks, Nikki. Here's some code I just created based on her excellent start. Instead of creating a recordset I create an array. I defined a Private module level variable in the declarations area to hold my array. When you process the array start with 1, NOT zero.

You need to set l_iColumn As Integer = 1 before starting Nikki's code because Excel is 1 based, not zero based like VBA.

*********************** Begin Code ***********************

Private Sub ProcessExcelWorkbook(pstrFileName As String)
Dim objXL As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Set objXL = CreateObject("Excel.Application&quot
Set xlWB = objXL.Workbooks.Open(pstrFileName)
Set xlWS = xlWB.Worksheets(1) 'I Always want first one
objXL.Visible = True
xlWS.Activate

GetColumnNamesArray xlWS

xlWB.Close False 'Donot save any changes
objXL.Quit

Set xlWS = Nothing
Set xlWB = Nothing
Set objXL = Nothing
End Sub ' ProcessExcelWorkbook

Private Sub GetColumnNamesArray(pxlWS As Worksheet)
Dim intCol As Integer
intCol = 1
Do Until pxlWS.Cells(1, intCol) = vbNullString
If IsEmpty(mavarColumnNamesArray) Then
ReDim mavarColumnNamesArray(intCol)
Else
ReDim Preserve mavarColumnNamesArray(intCol)
End If
mavarColumnNamesArray(intCol) = _
pxlWS.Cells(1, intCol).Text
intCol = intCol + 1
Loop
End Sub 'GetColumnNamesArray

**************************** End Code ********************

 

[dpimental]

Thanks Nikki.

Yeah once I set the l_iColumn = 1, it ran great.

SBendBuckeye, that's a good idea to put it in an array.

Either of you two know what's the most efficient (least amount of code) way to search a directory listing for ".xls" files return the path and file names in order to pass them through your procedure?

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[Nikita6003]

Sorry about that - was quite late when i posted tho' that's no xcuse ;-)

Try the VBA Dir function

ReDim l_sFileName(0)
'Set the Dir to the folder you want to search
l_sFileName(UBound(l_sFileName)) = Dir("C:\Excel\*.xls)

Do Until l_sFileName(UBound(l_sFileName)) = ""
  ReDim Preserve l_sFileName(UBound(l_sFileName)+1)
  'Using DIR without parameters loops tru' the filelist
  l_sFileName(UBound(l_sFileName)) = Dir
Loop

One thing: the Dir also returns the single and double subdirectory dots

HTH

Cheers
Nikki

 

[dpimental]

Thanks, I'll give it a try David Pimental
(US, Oh)
dpimental@checkfree.com