Xin code tự động nhập địa chỉ ô của Parameter Value trong Query Excel

One Response

1. hands says:

   31/01/2023 at 2:05 pm

   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:QueryBook1`.Name Name
   WHERE (Name.name='hang')
   2798
   nhưng chưa đúng ý em bác à.
   2799
   Đâ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é

   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:QueryBook1`.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ự

   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 à

   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.

   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

   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

   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:QueryBook1.xls;DefaultDir=C:Query;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
   , Destination:=Range("D2"))
   .CommandText = Array( _
   "SELECT Name.Name, Name.`Nam sinh` FROM `C:QueryBook1`.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:QueryQuery from Excel Files.dqy"
   .Refresh BackgroundQuery:=False
   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}"

   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 {}.

   Đượ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.

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

   Cái vấn đề này rất hay, nhưng hôm nay em lại bị dính một cái:

   Nếu bình thường ở một số máy thì chạy ngon lành, nhưng một số máy thì cái Application.SendKeys thì chả chạy gì cả, các bác giúp em với. Đây là code của em:

   Sub CD_THE()
       Sheets("THE").Select
       Range("F4").Select
   With ActiveSheet.QueryTables.Add(Connection:= _
           "ODBC;DSN=Excel Files;DBQ=M:MH.xls;DefaultDir=M:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
           , Destination:=Range("F4"))
           .CommandText = Array( _
           "SELECT Sum(THNXT.SLDN) AS 'Sum of SLDN'  FROM " & "`" & Sheets("SQL").Range("B2").Value & "`" & ".THNXT THNXT  WHERE (THNXT.TK=?) AND (THNXT.MSP=?)" _
           )
          [COLOR="#FF0000"] Application.SendKeys "{left}{left}{left}{TAB}{ }{TAB}{TAB}{Enter}"
           Application.SendKeys "{down}{left}{left}{left}{TAB}{ }{TAB}{TAB}{Enter}"[/COLOR]
           .Name = "30"
           .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 = _
           "" & Sheets("SQL").Range("B4").Value & "Query" & "30.dqy"
           .Refresh BackgroundQuery:=False
       End With
       End Sub

   Reply