Truy vấn dữ liệu từ file đóng sang file mở " lỗi tại câu truy vấn"

[maytinhvp01]

Mình coppy file trên mạng. về sửa lại theo cái mình hiểu để test thử nhưng gặp phải lối:

Sub TongHop()
    Dim cnn As Object, lsSQL As String, lrs As Object
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    cnn.provider = "microsoft.ACE.OLEDB.12.0" ' tam thoi ap dung cho excel 2007
    cnn.connectionstring = "data source = " & ThisWorkbook.Path & "\HN450.xls; extended properties = excel 12.0"
    cnn.cursorlocation = 3 ' thay cho aduseclient
    cnn.Open
            'Cau lenh truy van
            lsSQL = "SELECT * FROM [dongia$A2:G65536] WHERE f2 is not Null"
           [COLOR=#ff0000] lrs.Open lsSQL, cnn, 3, 1[/COLOR]
            'Copy ket qua vao sheet Tong hop
            Sheet1.Range("A65536").End(3).Offset(1, 0).CopyFromRecordset lrs
    cnn.Close
    Set lrs = Nothing
    Set cnn = Nothing
End Sub

file mở là book12, file dong là HN450.xls
muốn lấy dữ liệu từ file HN450 về book12
Xin cho hỏi:
1> lỗi trên là vì sao ạ

[COLOR=#ff0000] lrs.Open lsSQL, cnn, 3, 1[/COLOR]

2> số 3 và số 1 ở trên mình muốn hiểu hơn bạn giúp mình với

 

[maytinhvp01]

 lsSQL = "SELECT * FROM [dongia$A2:G65536] WHERE f2 is not Null"

câu lệnh này mình thử bỏ đi còn lại:

lsSQL = "SELECT * FROM [dongia$A2:G65536]

thì chạy ok, mình biết ngụ ý của tác giả muốn thêm điều kiện dữ liều của record tại cột F2 là không trống.
Vậy chỗ này phải sủa sao cho đúng ạ

 

[ndu96081631]

Mình coppy file trên mạng. về sửa lại theo cái mình hiểu để test thử nhưng gặp phải lối:

Sub TongHop()
    Dim cnn As Object, lsSQL As String, lrs As Object
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    cnn.provider = "microsoft.ACE.OLEDB.12.0" ' tam thoi ap dung cho excel 2007
    cnn.connectionstring = "data source = " & ThisWorkbook.Path & "\HN450.xls; extended properties = excel 12.0"
    cnn.cursorlocation = 3 ' thay cho aduseclient
    cnn.Open
            'Cau lenh truy van
            lsSQL = "SELECT * FROM [dongia$A2:G65536] WHERE f2 is not Null"
           [COLOR=#ff0000] lrs.Open lsSQL, cnn, 3, 1[/COLOR]
            'Copy ket qua vao sheet Tong hop
            Sheet1.Range("A65536").End(3).Offset(1, 0).CopyFromRecordset lrs
    cnn.Close
    Set lrs = Nothing
    Set cnn = Nothing
End Sub

file mở là book12, file dong là HN450.xls
muốn lấy dữ liệu từ file HN450 về book12
Xin cho hỏi:
1> lỗi trên là vì sao ạ

[COLOR=#ff0000] lrs.Open lsSQL, cnn, 3, 1[/COLOR]

2> số 3 và số 1 ở trên mình muốn hiểu hơn bạn giúp mình với

Tôi không rành ADO lắm nhưng "đoán" và "tự mò" cũng ra!
Do code của bạn viết không đầy đủ, thiếu bạn khai báo có lấy Header hay không nên tôi đoán rằng mặc định ADO sẽ xem như dữ liệu là CÓ TIÊU ĐỀ
Mà dữ liệu đã được xem là CÓ TIÊU ĐỀ thì phải ghi tên tiêu đề đàng hoàng (cái vụ F1, F2, F3... chỉ áp dụng cho trường hợp dữ liệu KHÔNG CÓ TIÊU ĐỀ thôi)
Vậy cần phải sửa lại vùng dữ liệu là dongia$A1:G65536 và thay chổ F2 bằng MSDM (chính là tiêu đề cột 2)
Cuối cùng là vầy:

lsSQL = "SELECT * FROM [dongia$A[COLOR=#ff0000]1[/COLOR]:G65536] WHERE [COLOR=#ff0000]MSDM[/COLOR] is not Null"

Bạn thử xem

 

[nmhung49]

 lsSQL = "SELECT * FROM [dongia$A2:G65536] WHERE f2 is not Null"

câu lệnh này mình thử bỏ đi còn lại:

lsSQL = "SELECT * FROM [dongia$A2:G65536]

thì chạy ok, mình biết ngụ ý của tác giả muốn thêm điều kiện dữ liều của record tại cột F2 là không trống.
Vậy chỗ này phải sủa sao cho đúng ạ

Bạn sửa chỗ này

cnn.connectionstring = "data source = " & ThisWorkbook.Path & "\HN450.xls; extended properties = excel 12.0"

Thành

cnn.connectionstring = "data source = " & ThisWorkbook.Path & "\HN450.xls; extended properties =""excel 12.0;HDR=No;"";"

Vì có liên quan tới F2 nên phải khai báo thêm HDR=No để cho nó hiểu, lâu quá ko sử dụng lại nên mình không biết giải thích thêm sao. Bạn kiểm tra lại giúp xem đúng không

 

[maytinhvp01]

Tôi không rành ADO lắm nhưng "đoán" và "tự mò" cũng ra!
Do code của bạn viết không đầy đủ, thiếu bạn khai báo có lấy Header hay không nên tôi đoán rằng mặc định ADO sẽ xem như dữ liệu là CÓ TIÊU ĐỀ
Mà dữ liệu đã được xem là CÓ TIÊU ĐỀ thì phải ghi tên tiêu đề đàng hoàng (cái vụ F1, F2, F3... chỉ áp dụng cho trường hợp dữ liệu KHÔNG CÓ TIÊU ĐỀ thôi)
Vậy cần phải sửa lại vùng dữ liệu là dongia$A1:G65536 và thay chổ F2 bằng MSDM (chính là tiêu đề cột 2)
Cuối cùng là vầy:

lsSQL = "SELECT * FROM [dongia$A[COLOR=#ff0000]1[/COLOR]:G65536] WHERE [COLOR=#ff0000]MSDM[/COLOR] is not Null"

Bạn thử xem

Vâng ạ thầy nhắc tới cái tiêu đề như bạn ở bài #4 nói thì em sẽ tìm hiểu thêm về cái HDR=No
em cảm ơn thầy nhé!!!

 

[maytinhvp01]

Bạn sửa chỗ này

cnn.connectionstring = "data source = " & ThisWorkbook.Path & "\HN450.xls; extended properties = excel 12.0"

Thành

cnn.connectionstring = "data source = " & ThisWorkbook.Path & "\HN450.xls; extended properties =""excel 12.0;HDR=No;"";"

Vì có liên quan tới F2 nên phải khai báo thêm HDR=No để cho nó hiểu, lâu quá ko sử dụng lại nên mình không biết giải thích thêm sao. Bạn kiểm tra lại giúp xem đúng không

Thank bạn nhé. Bạn nói thêm về cái HDR=No này đi: chút nữa là mình hiêu thôi và chác cũng có cả HDR=YES
thì tác dùng???

 

[hungpecc1]

Thank bạn nhé. Bạn nói thêm về cái HDR=No này đi: chút nữa là mình hiêu thôi và chác cũng có cả HDR=YES
thì tác dùng???

Trong 1 mô hình kiểu CSDL : có các dòng,và trường (cột) --> ADO sẽ tự động lấy hàng đầu tiên trong CSDL làm tên của các trường --> việc bạn khai báo HDR =No --> tức là bạn sẽ truy vấn dữ liệu không qua tên của trường mà qua số thứ tự của trường :
ví dụ : f1, f2 là cột thứ 1 thứ 2
nếu HDR =Yes --> bạn phải viết đúng tên cột , tương đương với ô đầu tiên của cột mà bạn muốn truy vấn

** Lưu ý nếu dùng ADO để lấy dữ liệu, thông thường ta hay thêm 1 thuộc tính nữa là IMEX=1 --> điều này sẽ giúp bảo toàn dữ liệu bạn đầu !

 

[nmhung49]

Nói tới HDR=Yes hoặc No thì có liên quan tới vùng dữ liệu bạn kết nối để lấy dữ liệu nếu vùng kết nối có đầy đủ dòng tiêu đề và không trùng và định dạng dữ liệu chuẩn tức là mỗi cột (Fields) chỉ có 1 định dạng duy nhất thì nên dùng HDR=Yes, ngược lại dùng HDR=No. Khi dùng HDR=No thì liên quan tới F1,F2...Fn. Trong đó F1 tương đương trường thứ nhất còn gọi là cột dữ liệu thứ nhất trong vùng kết nối cứ thế ta hiều F2....Fn. Ma tùy theo lúc bạn làm không được HDR=Yes thì tự động đổi thành No lúc đó sẽ OK, bạn tìm hiểu thêm nhen. Nói chung tuy theo dữ liệu mà bạn làm cho đúng. Cách nào cũng được miễn là đạt mục đích. Trong đó cách anh NDu là 1 ví dụ

 

[ndu96081631]

Thank bạn nhé. Bạn nói thêm về cái HDR=No này đi: chút nữa là mình hiêu thôi và chác cũng có cả HDR=YES
thì tác dùng???

Sửa lại toàn bộ code nè:

Sub TongHop2()
  Dim cnn As Object, lrs As Object
  [COLOR=#ff0000]Dim lVer As Long[/COLOR]
  [COLOR=#ff0000]Dim szConn As String[/COLOR], lsSQL As String, [COLOR=#ff0000]FileName As String[/COLOR]
  Set cnn = CreateObject("ADODB.Connection")
  Set lrs = CreateObject("ADODB.Recordset")
  [COLOR=#ff0000]lVer = Val(Application.Version)[/COLOR]
 [COLOR=#ff0000] FileName = ThisWorkbook.Path & "\HN450.xls"
  If lVer < 12 Then
  [/COLOR][COLOR=#ff0000]  szConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";" & _
                 "Extended Properties=""Excel 8.0;HDR=[/COLOR][B][COLOR=#0000cd]No[/COLOR][/B][COLOR=#ff0000];IMEX=1"";"
  Else
    szConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";" & _
                 "Extended Properties=""Excel 12.0;HDR=[/COLOR][B][COLOR=#0000cd]No[/COLOR][/B][COLOR=#ff0000];IMEX=1"";"[/COLOR][COLOR=#ff0000]
  End If[/COLOR]
  cnn.Open szConn
  lsSQL = "SELECT * FROM [dongia$A2:G65536] WHERE F2 is not Null"
 [COLOR=#0000cd] 'Neu HDR=[B]Yes[/B] thì cau lênh sua thành:
  'lsSQL = "SELECT * FROM [dongia$A1:G65536] WHERE MSDM is not Null"[/COLOR]
  lrs.Open lsSQL, cnn, 3, 3
  Sheet1.Range("A65536").End(3).Offset(1, 0).CopyFromRecordset lrs
  cnn.Close
  Set lrs = Nothing
  Set cnn = Nothing
End Sub

Sửa như vậy bạn sẽ tùy biến thoải mái hơn cho những file khác:
- FileName là 1 biến riêng để thiết lập đường dẫn đến file cần lấy dữ liệu
- Biến szConn được thiết lập để có thể xài ở cả Office 2003 và Office 2007
- Lưu ý chổ đánh dấu màu xanh nhé

 

[maytinhvp01]

Sửa lại toàn bộ code nè:

Sub TongHop2()
  Dim cnn As Object, lrs As Object
  [COLOR=#ff0000]Dim lVer As Long[/COLOR]
  [COLOR=#ff0000]Dim szConn As String[/COLOR], lsSQL As String, [COLOR=#ff0000]FileName As String[/COLOR]
  Set cnn = CreateObject("ADODB.Connection")
  Set lrs = CreateObject("ADODB.Recordset")
  [COLOR=#ff0000]lVer = Val(Application.Version)[/COLOR]
 [COLOR=#ff0000] FileName = ThisWorkbook.Path & "\HN450.xls"
  If lVer < 12 Then
  [/COLOR][COLOR=#ff0000]  szConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";" & _
                 "Extended Properties=""Excel 8.0;HDR=[/COLOR][B][COLOR=#0000cd]No[/COLOR][/B][COLOR=#ff0000];IMEX=1"";"
  Else
    szConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";" & _
                 "Extended Properties=""Excel 12.0;HDR=[/COLOR][B][COLOR=#0000cd]No[/COLOR][/B][COLOR=#ff0000];IMEX=1"";"[/COLOR][COLOR=#ff0000]
  End If[/COLOR]
  cnn.Open szConn
  lsSQL = "SELECT * FROM [dongia$A2:G65536] WHERE F2 is not Null"
 [COLOR=#0000cd] 'Neu HDR=[B]Yes[/B] thì cau lênh sua thành:
  'lsSQL = "SELECT * FROM [dongia$A1:G65536] WHERE MSDM is not Null"[/COLOR]
  lrs.Open lsSQL, cnn, 3, 3
  Sheet1.Range("A65536").End(3).Offset(1, 0).CopyFromRecordset lrs
  cnn.Close
  Set lrs = Nothing
  Set cnn = Nothing
End Sub

Sửa như vậy bạn sẽ tùy biến thoải mái hơn cho những file khác:
- FileName là 1 biến riêng để thiết lập đường dẫn đến file cần lấy dữ liệu
- Biến szConn được thiết lập để có thể xài ở cả Office 2003 và Office 2007
- Lưu ý chổ đánh dấu màu xanh nhé

Lời cảm ơn này tới anh và cả hai bạn nữa!!!!!!!

 

[maytinhvp01]

Trong 1 mô hình kiểu CSDL : có các dòng,và trường (cột) --> ADO sẽ tự động lấy hàng đầu tiên trong CSDL làm tên của các trường --> việc bạn khai báo HDR =No --> tức là bạn sẽ truy vấn dữ liệu không qua tên của trường mà qua số thứ tự của trường :
ví dụ : f1, f2 là cột thứ 1 thứ 2
nếu HDR =Yes --> bạn phải viết đúng tên cột , tương đương với ô đầu tiên của cột mà bạn muốn truy vấn

** Lưu ý nếu dùng ADO để lấy dữ liệu, thông thường ta hay thêm 1 thuộc tính nữa là IMEX=1 --> điều này sẽ giúp bảo toàn dữ liệu bạn đầu !

Sub slinsert()
Dim cn As Object
Dim rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
With cn
    .ConnectionString = "provider=microsoft.ACE.OLEDB.12.0;" & _
                        "data source=" & ThisWorkbook.Path & "\data.xlsm" & _
                        ";extended properties=""excel 12.0;HDR=No;[COLOR=#ff0000]IMEX=1[/COLOR]"";"
    '.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & ThisWorkbook.Path & _
                        "\data.xlsm;Extended Properties=""Excel 12.0;HDR=No;"""
    .Open
    .Execute "INSERT INTO [sheet1$] SELECT f1,f2,f3 FROM [excel 12.0;database=" & _
                ThisWorkbook.FullName & ";HDR=No].[sheet1$A1:D10]"
End With
cn.Close: Set cn = Nothing
End Sub

Cái vấn đề này được bạn nhắc tới rồi nhưng giờ mới gặp trường hợp này không biết có đúng cái bạn muốn hưỡng dẫn mình không? code trên mình viết có IMEX=1 phất sinh lỗi. Vậy mình hiểu thế này đúng không bạn:
Thuộc tính IMEX=1 để bảo toàn dữ liệu file mình kết nối tới, nhưng ở đây mình chèn dữ liệu vào file mình kết nối tới nên dùng IMEX=1 đã làm cản trở việc ghi dữ liệu nên phát sinh lỗi phải không bạn?