Results 1 to 3 of 3

Thread: Updating Command Text Query from Excel using VBA.

  1. #1
    Neophyte Kallie's Avatar
    Join Date
    Oct 2015
    Location
    Johannesburg
    Posts
    3
    Articles
    0

    Updating Command Text Query from Excel using VBA.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good Afternoon,

    I would appreciate any assistance with the following matter.

    I am currently working on a tracking document in Excel that displays the table from our SQL Server. My Database name is Active Qmatic and table name is Active_Qmatic with the following column names:

    Country
    Branch
    [Problem / Request Status]
    [Quote Status / Requirements]
    [Stock Order]
    [Shipment Number]
    [Change Control]
    Date
    [Priority Rating]
    Comments

    All column data types are nvarchar(50) except for Date which is a data type date and Comments which is a data type nvarchar(max). The table pulls through to the excel document via a OLE DB Query Connection Type displaying all the details. What I am trying to do now is to use my date selector created in VBA to update the Command Text under Connection Excel to update the Query based on the cell value B3 and C3 from sheet4 which respectively acts as the From Date and To Date of information to display that falls between the date selected. Can someone provide some guidance on how to go about writing this in VBA as all my attempts failed. The table information I would like returned is not of a pivot nature as all the data displayed are strings with exception to the date which is not able to be pivoted without manipulation. I also want a button to facilitate the submitting(Refressing) of the Query after the date was selected.

    Kind regards

    Kallie

  2. #2
    Neophyte Kallie's Avatar
    Join Date
    Oct 2015
    Location
    Johannesburg
    Posts
    3
    Articles
    0
    Hi There,

    Instead of using VBA to update the command text I have reverted to using an ODBC connection and database query with parameters. I am using the following query:

    SELECT Future_Qmatic.Country, Future_Qmatic.Branch, Future_Qmatic.Description, Future_Qmatic."Quote Status / Raised", Future_Qmatic."Stock Order", Future_Qmatic."Shipment Number", Future_Qmatic."Go Live Date", Future_Qmatic.Date, Future_Qmatic."Priority Rating", Future_Qmatic.Comments

    FROM "Future Qmatic".dbo.Future_Qmatic Future_Qmatic

    WHERE (Future_Qmatic.Date >= ?) and (Future_Qmatic.Date <= ?) and (Future_Qmatic.Country = ?)

    My country parameter gets its value from a drop down list containing all the countries with another option called All for all the countries and the dates are selected from a VBA date selector I created tied to certain cells. I want to know if it is possible to structure the query in such a way that if the "All" country option is selected then the table information for all countries will be returned or when a country is selected then only that country based on the date selected will be returned. Is this possible?

    Any assistance or recommendations will be highly appreciated.

    Kallie

  3. #3
    Neophyte Kallie's Avatar
    Join Date
    Oct 2015
    Location
    Johannesburg
    Posts
    3
    Articles
    0
    Hi Everyone,

    I resolved the issue. Anybody interested in the resolution can let me know and I will let you know.

    Kallie

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •