Xin code tự động nhập địa chỉ ô của Parameter Value trong Query Excel (1 người xem)

[nmh_phocu]

Em xin các anh chị tư vấn code cho em trường hợp này:

Em tạo một query trong excel có dạng SQL như sau :
SELECT Name.Name, Name.`Nam sinh`
FROM `C:\Query\Book1`.Name Name
WHERE (Name.Name=?)
rồi em ghi lại Query này ra chỗ khác

Cái dấu "?" này là địa chỉ ô Parameter Value của Query em phải nhập.

Sau đó em dùng ghi Macro để ghi lại việc em nhập Query này vào bảng excel của mình và em thấy code như sau:



Sub Macro()
Sheets("Sheet1").Select
Range("D2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Excel Files;DBQ=C:\Query\Book1.xls;DefaultDir=C:\Query;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("D2"))
.CommandText = Array( _
"SELECT Name.Name, Name.`Nam sinh` FROM `C:\Query\Book1`.Name Name WHERE (Name.Name=?)" _
)
.Name = "Query from Excel Files_2"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.SourceConnectionFile = "C:\Query\Query from Excel Files.dqy"
.Refresh BackgroundQuery:=False
End With
End Sub


Ở chỗ em bôi đỏ ở trên trong code em muốn thay bằng địa chỉ ô cụ thể thì phải nhập cái gì vào ạ? Ví dụ em muốn nhập thay dấu chấm hỏi đó là địa ô C2 để khi đó chạy Macro máy sẽ tự động hiểu "?" là ô C2 mà không bắt mình nhập vào Parameter Value nữa.

Em xin chân thành cảm ơn các bác trước

em gửi thêm cái file, các bác giải nén vào ổ C nhé

 

[ptm0412]

"SELECT Name.Name, Name.`Nam sinh` FROM `C:\Query\Book1`.Name Name WHERE (Name.Name=?)" _
)

Bạn thử thay bằng

"SELECT Name.Name, Name.`Nam sinh`  FROM `C:\Query\Book1`.Name Name  WHERE ([COLOR=black]Name.Name=[/COLOR])"  [COLOR=red][B]& [C2][/B][/COLOR] _
        )

 

[nmh_phocu]

Bạn thử thay bằng

"SELECT Name.Name, Name.`Nam sinh`  FROM `C:\Query\Book1`.Name Name  WHERE ([COLOR=black]Name.Name=[/COLOR])"  [COLOR=red][B]& [C2][/B][/COLOR] _
        )

Em cảm ơn bác vì bác đã quan tâm, nhưng vẫn không được anh à, nó vẫn báo lỗi khi em thay vào như bác, bác cố gắng giúp em với

 

[ptm0412]

Xin lỗi, tôi gõ nhầm: Sai vị trí dấu ngoặc và thiếu cặp dấu nháy đơn.

        "SELECT Name.Name, Name.`Namsinh`  FROM `C:\Query\Book1`.Name Name  WHERE (Name.Name= [COLOR=red]'[/COLOR]" & [c2] & "[COLOR=red]'[/COLOR][COLOR=red])[/COLOR]" _
        )

 

[nmh_phocu]

Xin lỗi, tôi gõ nhầm: Sai vị trí dấu ngoặc và thiếu cặp dấu nháy đơn.

        "SELECT Name.Name, Name.`Namsinh`  FROM `C:\Query\Book1`.Name Name  WHERE (Name.Name= [COLOR=red]'[/COLOR]" & [c2] & "[COLOR=red]'[/COLOR][COLOR=red])[/COLOR]" _
        )

Rất rất cảm ơn bác em đã làm được rồi

 

[nmh_phocu]

Rất rất cảm ơn bác em đã làm được rồi

Bác thông cảm lúc đầu tưởng được nhưng vẫn không được bác à, có lỗi phát sinh là khi em vào xem cái SQL sau khi cài là :
SELECT Name.name, Name.Namsinh
FROM `C:\Query\Book1`.Name Name
WHERE (Name.name='hang')

nhưng chưa đúng ý em bác à.

Đây là lấy giá trị của ô C2 đang có, ý em hỏi là mình không phải sử dụng tay khi query hỏi địa chỉ ô Parameter Value cơ địa chỉ ô của Parameter Value, máy tính tự động nhập vào ô Enter Parameter Value, Parameter =Sheet1!$C$2 và đánh dấu vào Use this value/ reference for future refreshes . Đó chính xác là cái em nhờ bác. Em gửi cả hình ảnh, bác giúp em với nhé

 

[ptm0412]

Bạn thử đổi C2 từ hung thành hang, hoặc ngược lại, rồi chạy code xem?
Nếu cần bạn thêm vào code dòng lệnh xoá các dòng dữ liệu cũ, chép đè dữ liệu mới lên.
Muốn khi thay C2, code tự chạy 1 cách tự động thì đưa vào sự kiện Workbook_Change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
    For Each nm In ActiveWorkbook.Names
        If Left(nm.Name, 14) = "Sheet1!MyQuery" Then Range(nm).Clear: nm.Delete
    Next
    MakeQuery
End If
End Sub

TB:

Bạn xài add-in hay phiên bản nào mà ra cái hộp thoại như hình vậy?

 

[nmh_phocu]

Bạn thử đổi C2 từ hung thành hang, hoặc ngược lại, rồi chạy code xem?
Nếu cần bạn thêm vào code dòng lệnh xoá các dòng dữ liệu cũ, chép đè dữ liệu mới lên.
Muốn khi thay C2, code tự chạy 1 cách tự động thì đưa vào sự kiện Workbook_Change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
    For Each nm In ActiveWorkbook.Names
        If Left(nm.Name, 14) = "Sheet1!MyQuery" Then Range(nm).Clear: nm.Delete
    Next
    MakeQuery
End If
End Sub

TB:

Bạn xài add-in hay phiên bản nào mà ra cái hộp thoại như hình vậy?

Vẫn không ổn bác ạ, cái hộp thoại đó là khi em lập cái query (cài sẵn trong Excl đó) với điều kiện là lọc các điều kiện theo dữ liệu tại ô C2 đó bác.

khi làm query với điều kiện chọn ô thì khi làm xong nó sẽ hỏi mình chọn cái ô nào có dữ kiện

Làm theo kiểu của bác thì mỗi lần thay đổi thì nó làm lại query, đây trường hợp của em chỉ muốn là cài query một lần thôi tức là cài cho chạy một lần rồi khi thay đổi ô C2 em chỉ cần ấn nút có dấu than màu đỏ là được, không xoá và nhập lại query như bác nữa, như thế đỡ tốn bộ nhớ ram.

EM chụp ảnh từng bước em làm cái query nhé

Đầu tiên em đã có sẵn cái querry cho File Book1 rồi : File Query from Excel Files.dqy với SQL là :
SELECT Name.name, Name.Namsinh
FROM `C:\Query\Book1`.Name Name
WHERE (Name.name=?)

Sau đó em chạy code VBA trong Book1 của em: Nó nhập cái Query from Excel Files.dqy vào excel của em, rồi nó nhẩy ra hộp thoại để em chọn ô C2 là ô đưa vào dữ kiện : hung hoặc hang.

Từ lần sau thì em chỉ cần thay hung hoặc hang hoặc bất kỳ thứ gì rồi nhấn nút chấm than màu đỏ là nó tìm dữ liệu và trả lại chuẩn.

Cái em hy vọng là khi em cài cái query đó băng code VBA thì nó tự động nhận ô C 2 là ô sẽ chứa các điều kiện tìm kiếm. Thank bác nhiều, bác giúp em với

À, với cách làm của bác thì mỗi lần thay đổi thì vào hộp Name thì name query lại tăng thêm 1 số thứ tự

 

[ptm0412]

Xét theo hình bạn đưa lên và cách bạn giải thích, thì bạn đã làm xong rồi còn gì?
Name tăng 1 số nhưng số bao nhiêu tôi cũng xoá đi được, lúc nào cũng chỉ tồn tại 1 name.
Tôi chưa kiểm tra được tạo lại query hay refresh query có sẵn cái nào nhanh hơn, nhưng, chắc chắn 1 điều là không khác nhau về việc tốn ram. Bản chất refresh query cũng là chạy lại câu lệnh SQL với toàn bộ dữ liệu. Tạo mới query cũng thế.
Huống hồ refresh với tham số thay đổi, cũng như tạo mới còn gì.

 

[nmh_phocu]

Xét theo hình bạn đưa lên và cách bạn giải thích, thì bạn đã làm xong rồi còn gì?
Name tăng 1 số nhưng số bao nhiêu tôi cũng xoá đi được, lúc nào cũng chỉ tồn tại 1 name.
Tôi chưa kiểm tra được tạo lại query hay refresh query có sẵn cái nào nhanh hơn, nhưng, chắc chắn 1 điều là không khác nhau về việc tốn ram. Bản chất refresh query cũng là chạy lại câu lệnh SQL với toàn bộ dữ liệu. Tạo mới query cũng thế.
Huống hồ refresh với tham số thay đổi, cũng như tạo mới còn gì.

Làm thì xong rồi bác, nhưng em muốn cho nó tự động ở cái phần phải nhập Enter Parameter Value, bởi em có rất nhiều cái query như vậy. Tiếp đến là nếu mà sử dụng mỗi lần nhập lại thì em phải lưu lại tất cả các query đó, mà như vậy thì sẽ nặng máy hơn, tiếp đến là việc bảo mật sẽ kém hơn, tiếp đến là nếu ai lỡ xóa một cái query của mình thì hết phim mời đồng bào về nghỉ bác à, chính vì các lý do như vậy nên em mới loay hoay tìm cách để tự động nhập vào hộp thoại parameter value. bởi một chương trình nho nhỏ của em có tận 60 lần phải nhập tay cái Enter Parameter Value, mất nhiều thời gian bác à

 

[ptm0412]

Tôi sử dụng lại file gốc của bạn, chạy code 1 lần, hiện thông báo như cái hình của bạn, chọn C2, đánh dấu vào 2 ô vuông.

Thế là chạy y như bạn muốn và còn hơn nữa: Khi thay giá trị C2, lập tức Query tự refresh, khỏi nhấn refresh bằng chuột.

Vậy bạn muốn gì hơn nữa?

 

[nmh_phocu]

Tôi sử dụng lại file gốc của bạn, chạy code 1 lần, hiện thông báo như cái hình của bạn, chọn C2, đánh dấu vào 2 ô vuông.

Thế là chạy y như bạn muốn và còn hơn nữa: Khi thay giá trị C2, lập tức Query tự refresh, khỏi nhấn refresh bằng chuột.

Vậy bạn muốn gì hơn nữa?

Cái hay là em muốn cái chọn C2 và đánh vào 2 ô vuông là tự động bác à, em không phải kick chuột nữa
Dạ, hoàn toàn gần đúng ý em , còn một chút nữa thôi là theo cách của em thì em có thể xoá cái query đó đi mà file excel vẫn chạy bình thường (không phải cài lại cái query nữa - theo code của bác). em muốn là khi em cài lần đầu em không phải dùng chuột nhấn vào phím C2 (bởi vì với nhiều query thì em phải nhấn nhiều lần). Giảm công sức mà bác. Bác cố gắng làm theo ý tưởng của em tý, em hứa uống riêng với bác 3 ly đầy đủ, hay bác cho em số điện của Bác, em sẽ trình bày đầy đủ với bác, và em sẽ bật mý một cái rất hay về query.

 

[ptm0412]

Cái hay là em muốn cái chọn C2 và đánh vào 2 ô vuông là tự động bác à, em không phải kick chuột nữa

Chuyện này hầu như là không thể. Do câu SQL có 1 dấu ? nên bắt buộc phải ra cái hộp thoại hỏi. Excel và VBA chưa có cách nào đánh ô chọn (không phải lần nào cũng C2) và uýnh dấu chọn 2 ô checkbox, của 1 dialogbox chứ không phải userform.

Còn chuyện ai đó xoá query hoặc chính ta muốn xoá query trước khi đóng file cho nhẹ:

Dùng code của tôi thì mặc kệ ai muốn xoá thì xoá, còn trước khi đóng file thêm 1 câu lệnh xoá thì nó sẽ xoá (cả name và cả query). Sau đó chỉ cần điền nội dung vào C2 là có lại như cũ, hoặc muốn tự động thì cho chạy code khi mở file.

Dùng cái của bạn thì cũng chẳng cản được người ta xoá, và nếu người ta xoá cả query và cả name thì lấy cái gì mà refresh?

Cám ơn bạn có ý muốn trao đổi, tôi cũng muốn học thêm từ bạn. Xin vui lòng liên hệ Y!M thanhmy_pham, Skype ptm0412, Hand phone 0919 77 2142

 

[nmh_phocu]

Chuyện này hầu như là không thể. Do câu SQL có 1 dấu ? nên bắt buộc phải ra cái hộp thoại hỏi. Excel và VBA chưa có cách nào đánh ô chọn (không phải lần nào cũng C2) và uýnh dấu chọn 2 ô checkbox, của 1 dialogbox chứ không phải userform.

Còn chuyện ai đó xoá query hoặc chính ta muốn xoá query trước khi đóng file cho nhẹ:

Dùng code của tôi thì mặc kệ ai muốn xoá thì xoá, còn trước khi đóng file thêm 1 câu lệnh xoá thì nó sẽ xoá (cả name và cả query). Sau đó chỉ cần điền nội dung vào C2 là có lại như cũ, hoặc muốn tự động thì cho chạy code khi mở file.

Dùng cái của bạn thì cũng chẳng cản được người ta xoá, và nếu người ta xoá cả query và cả name thì lấy cái gì mà refresh?

Cám ơn bạn có ý muốn trao đổi, tôi cũng muốn học thêm từ bạn. Xin vui lòng liên hệ Y!M thanhmy_pham, Skype ptm0412, Hand phone 0919 77 2142

Em làm mọi cách vẫn không đạt được như ý muốn, đúng là pó tay rồi bác, thank bác rất rất nhiều

 

[ptm0412]

Dùng SendKeys với 1 chút tà đạo:

Sub Macro()
    Sheets("Sheet1").Select
    Range("C1").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Excel Files;DBQ=C:\Query\Book1.xls;DefaultDir=C:\Query;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("D2"))
        .CommandText = Array( _
        "SELECT Name.Name, Name.`Nam sinh`  FROM `C:\Query\Book1`.Name Name  WHERE (Name.Name=?)" _
        )
        With Application
            .SendKeys "{Down}"
            .SendKeys "{TAB}"
            .SendKeys " "
            .SendKeys "{TAB}"
            .SendKeys "chr(32)"
            .SendKeys "{Enter}"
        End With
        .Name = "Query from Excel Files_2"
        .FieldNames = True
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 60
        .PreserveColumnInfo = False
        .SourceConnectionFile = "C:\Query\Query from Excel Files.dqy"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[nghiaphuc]

Dùng SendKeys với 1 chút tà đạo:

Sub Macro()
'......
With Application
.SendKeys "{Down}"
.SendKeys "{TAB}"
.SendKeys " "
.SendKeys "{TAB}"
.SendKeys "chr(32)"
.SendKeys "{Enter}"
End With
'......
End Sub

Dãy lệnh Sendkeys ở trên có thể thay bởi 1 câu lệnh như vầy cho gọn được thầy ạ:

Application.SendKeys "{Down}{TAB} {TAB} {Enter}"

 

[ptm0412]

Dãy lệnh Sendkeys ở trên có thể thay bởi 1 câu lệnh như vầy cho gọn được thầy ạ:

Application.SendKeys "{Down}{TAB} {TAB} {Enter}"

Mình mới làm lần đầu í mà. Cám ơn NghiaPhuc nha.

Có lẽ nên viết vầy cho đúng quy tắc: tác vụ nhấn phím Space (khoảng trắng) gởi qua SendKeys cũng bao quanh bởi cặp dấu {}

Application.SendKeys "{Down}{TAB}{ }{TAB}{ }{Enter}"

To specify characters that aren't displayed when you press a key, such as ENTER or TAB, and keys that represent actions rather than characters, use the codes enclose them in { and }.

 

[nghiaphuc]

Mình mới làm lần đầu í mà. Cám ơn NghiaPhuc nha.

Có lẽ nên viết vầy cho đúng quy tắc: tác vụ nhấn phím Space (khoảng trắng) gởi qua SendKeys cũng bao quanh bởi cặp dấu {}

Application.SendKeys "{Down}{TAB}{ }{TAB}{ }{Enter}"

To specify characters that aren't displayed when you press a key, such as ENTER or TAB, and keys that represent actions rather than characters, use the codes enclose them in { and }.

Em đã thử cả 2 cách làm trên rồi, đều được hết thầy ạ. Còn trong câu tiếng Anh trên thì em hiểu là chỉ những ký tự mà khi ta gõ, nó không hiển thị trên màn hình, chẳng hạn khi ta nhấn phím TAB, ENTER, ESC, ALT,... thì mới bắt buộc phải bao bởi cặp dấu {}, còn phím Space thì khi ta nhấn, nó hiển thị 1 khoảng trắng (ví dụ thầy sử dụng các phím này khi đang chọn 1 textbox trên Form) nên ký tự này không cần thiết phải bao bởi cặp dấu {}.

 

[ptm0412]

Em đã thử cả 2 cách làm trên rồi, đều được hết thầy ạ. Còn trong câu tiếng Anh trên thì em hiểu là chỉ những ký tự mà khi ta gõ, nó không hiển thị trên màn hình, chẳng hạn khi ta nhấn phím TAB, ENTER, ESC, ALT,... thì mới bắt buộc phải bao bởi cặp dấu {}, còn phím Space thì khi ta nhấn, nó hiển thị 1 khoảng trắng (ví dụ thầy sử dụng các phím này khi đang chọn 1 textbox trên Form) nên ký tự này không cần thiết phải bao bởi cặp dấu {}.

Được tất nhiên là được, vì ở trên mình dùng " " và cả "Chr(32)" đều chạy. Còn câu tiếng Anh (mấychữ tô đậm) có nghĩa là:
Nếu những Keys được gởi đi mang ý nghĩa thực hiện 1 tác vụ (action) chứ không phải chuỗi ký tự (cần gõ vào khung text chằng hạn) thì nên bao lại bằng cặp dấu {}.
Vụ này do anh Bill Dự phòng cho trường hợp ta sendkeys đến cả những chương trình khác như word, notepad, IE, ... Và nhỡ mà những phần mềm đó hiểu sai là 1 khoảng trắng thay vì 1 tác vụ chọn/bỏ chọn cái checkbox.

 

[nmh_phocu]

Mình mới làm lần đầu í mà. Cám ơn NghiaPhuc nha.

Có lẽ nên viết vầy cho đúng quy tắc: tác vụ nhấn phím Space (khoảng trắng) gởi qua SendKeys cũng bao quanh bởi cặp dấu {}

Application.SendKeys "{Down}{TAB}{ }{TAB}{ }{Enter}"

To specify characters that aren't displayed when you press a key, such as ENTER or TAB, and keys that represent actions rather than characters, use the codes enclose them in { and }.

Em rất cảm ơn các anh, em hiểu rồi, mẹo hay quá