Ado Header not on first line 3

[LARiot]

Hi guys. Thanks for any help on this.

I'm opening an excel file using ADO the only thing is the header is not on the first line. There are about seven lines prior which are useless.

Thanks.

Function ImportForecast()
    Dim cn As ADODB.Connection
    Dim rsTable As ADODB.Recordset
    
    Dim varAllFiles As Variant
    Dim strSQL As String
    Dim strFile As String
    Dim i As Integer
    
    Set cn = CreateObject("ADODB.Connection")
    Set rsTable = CreateObject("ADODB.Recordset")
    
    varAllFiles = OpenFiles("Select Forecast file")
    If blnCancel = True Then Exit Function

    For i = 0 To UBound(varAllFiles)
        strFile = varAllFiles(i)
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;MaxScanRows=0;IMEX=0"";"
        strSQL = "SELECT * FROM [Data Fcst Input$]"
        rsTable.Open strSQL, cn

        Do While Not rsTable.EOF
            Debug.Print rsTable.Fields("Label") & " " & rsTable.Fields("Acct#")
                rsTable.MoveNext
        Loop
        rsTable.Close
        cn.Close
    Next
    
    DoCmd.SetWarnings True
    MsgBox "Done."
End Function

-Neema

 

[Remou]

This should help:

[tt]Specify an Unnamed Range

To specify an unnamed range of cells as your recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

strQuery = "SELECT * FROM [Sheet1$A1:B10]"[/tt]


--

http://support.microsoft.com/kb/257819

 

[LARiot]

The question is: Is there a way to tell Access to start on line eight and to treat it as a header?

Thanks.

-Neema

 

[Remou]

That is what this is about:

strQuery = "SELECT * FROM [Sheet1$A1:B10]"

So ..

strQuery = "SELECT * FROM [Sheet1$A8:B10]

 

[LARiot]

Remou, you're fast. You posted that as I was typing up my second remark.

Thanks it worked.

I do have another problem and I've searched google for it to no avail. One of the column headers in Excel contains the special character #. It says it Item cannot be found when it gets to this piece of code: rsTable.Fields("Acct#")

Thanks again.

-Neema

 

[Remou]

I think that braces will fix it:

rsTable.Fields("[Acct#]")

 

[LARiot]

Thanks again Remou. Turns out it was just missing a space between the Acct and the #.



-Neema

 

[JerryKlmns]

Onother option to ignore the first 8 lines of excel would be to move 8 records forward!

 

[LARiot]

No because then it would read the headers as data.

-Neema

 

[PWise]

to: LARiot and Remou I just gave both of you stars because I have grappled with this problem and have not found such a straight forward solution as this

Thank you

I have even advance this solution to my to my situation where the Worksheet is set up like this
Row 1 Header
Rows 2-4 totals
Rows 6-xxx Data

SELECT * FROM [Sheet3$a1:h1]union SELECT * FROM [Sheet3$a5:h26]

Note that even though that my data starts @ row 6 I still use the range A5:h26 because ADO uses the first row in each select as a header

 

[JerryKlmns]

No because then it would read the headers as data.

I think not, but let me explain it a little more what I was thinking

....
rsTable.Open strSQL, cn
rsTable.Move 8 'Here you skip the first 8 lines
Do While Not rsTable.EOF ' Now you start reading
....