CODEWITHSUNDEEP
excelvbams-accessdao
user6440081
user6440081

Reputation:

Get end of rows of an excel file within access vba

I'm importing an excel file into access using vba (dao) in the following manner:

Set db = CurrentDb    
query = "SELECT DISTINCT * INTO MyTable" _
          & " FROM [Excel 12.0 Xml;HDR=Yes;Database=" & filePath & "].[Sheet1$];"
db.Execute (query)

[Sheet1$] is the keyword here. My excel table header starts with line 3. I want to do something like [Sheet1$A3:Lastline].

Is there a simple way to obtain the lastline? Or do I really need to create a VBA Excel Object, open the file and count?

Alternatively, can I change the header start? For instance, by using a custom import scheme instead?

Thanks in advance.

Upvotes: 0

Views: 126

Answers (1)

Parfait
Parfait

Reputation: 107577

Consider a count query using a recordset and concatenate result in make-table query:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim query As String

Set db = CurrentDb 
Set rst = db.OpenRecordset("SELECT Count(*) AS RowCount" _
   & " FROM [Excel 12.0 Xml;HDR=No;Database=" & filePath & "].[Sheet1$]")

query = "SELECT DISTINCT * INTO MyTable" _ 
   & " FROM [Excel 12.0 Xml;HDR=Yes;" _
   & " Database=" & filePath & "].[Sheet1$A3:A" & rst!RowCount & "];" 

db.Execute (query)

rst.Close
Set rst= Nothing
Set db = Nothing

Upvotes: 2

Related Questions