Excel VBA- Import *.dbf to excel

P

pie

Please tell me,the VBA code to import *.dbf to active worksheet i
excel.
Now I use "QueryTable", this code(below), to import Access fil
(*.mdb) to active worksheet.
But when I choose *.dbf file , it's not work.
'*************************************
sqlstring = "select distinct name from MXTREND"
connstring = _
"ODBC;DSN=MXTREND;UID=Rep21;PWD=NUyHwYQI;Database=MXTREND"
Worksheets(ReplaceSheet).Activate
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"), Sql:=sqlstring)
.Refresh

End With
'******************************
thanks, Pi
 
N

Nigel

XL recognises .dbf as valid file type and will import it directly, you do
not need to open through a query.
Of course you are limited to the 65,535 rows (row 1 will contain the dbf
header record field names)

Workbooks.Open Filename:="C:\testdbf.dbf"

Cheers
Nigel
 
P

pie

Thanks a lot, but that is my next problem.
Because I want to filter data before paste to worksheet,
such as 'select * from test.dbf where name = "Philip" '.
The second, my *.dbf file has large data to paste.

Do you have any way to advise me
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top