Solved

Comma with unknown meaning in VBA for recorded macro in Excel to query SQL Server

Posted on 2010-09-17
6
346 Views
Last Modified: 2012-05-10
I'm tring to dress up some code that I recorded in Excel with the macro recorder.  This code queries SQL Server and returns a table to Excel.  The recorder inserted some commas at the end of a line that appear to have no meaning.  But, when I take them out, I get a type mismatch error.

I have added some & _ to the recorded code to make it easier to read.  But you can spot two places where there is  this set of characters  ", " that cannot be taken out.  Can anyone tell me what the purpose of the commas are?  

Here is the code in working condition.  Notice the comma in the middle of the word FROM for the TSQL from clause.  There is another comma in the middle of the word AND inside the TSQL where clause.  If I take these commas and the quotation marks that encapsulate the commas out of the code, the code  bombs with a type mismatch.  Does anyone know why?


Sub MultipleCustsRecorded()
   ActiveSheet.Cells.ClearContents
   
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=SQL1;UID=User;PWD=Password;" & _
        "APP=2007 Microsoft Office system;WSID=ZZZZZ;DATABASE=HJ_TestTBIData;" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT rptPreOrdersDetail.DueDate, rptPreOrdersDetail.RouteNo, rptPreOrdersDetail.CustNo, " & _
            "rptPreOrdersDetail.ItemNo, rptPreOrdersDetail.Package, rptPreOrdersDetail.Brand, " & _
            "rptPreOrdersDetail.CaseQty" & Chr(13) & "" & Chr(10) & _
            "F", "ROM HJ_TestTBIData.dbo.rptPreOrdersDetail rptPreOrdersDetail" & Chr(13) & "" & Chr(10) & _
        "WHERE (rptPreOrdersDetail.CustNo=29890) AND (rptPreOrdersDetail.DueDate={ts '2010-09-15 00:00:00'}) " & _
            "OR (rptPreOrdersDetail.CustNo=28765) " & _
            "A", "ND (rptPreOrdersDetail.DueDate={ts '2010-09-15 00:00:00'})" & Chr(13) & "" & Chr(10) & _
            "ORDER BY rptPreOrdersDetail.Package, rptPreOrdersDetail.ItemNo")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_HJPreOrders"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

0
Comment
Question by:Lynn Huff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33702794
Your SQL string is being built up using an Array and the commas are separating the elements of the array. You could simply remove the array and use one long string.
0
 
LVL 3

Author Comment

by:Lynn Huff
ID: 33703591
Rorya, I tried your suggestion.  The result was a "syntax error."
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33703976
What exactly did you use?
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 3

Author Comment

by:Lynn Huff
ID: 33704161
This is the code i tried that produced a syntax error.
Sub MultipleCustsRecorded()
   ActiveSheet.Cells.ClearContents
   
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=SQL1;UID=User;PWD=Password;" & _
        "APP=2007 Microsoft Office system;WSID=ZZZZZ;DATABASE=HJ_TestTBIData;" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = "SELECT rptPreOrdersDetail.DueDate, rptPreOrdersDetail.RouteNo, rptPreOrdersDetail.CustNo, " & _
            "rptPreOrdersDetail.ItemNo, rptPreOrdersDetail.Package, rptPreOrdersDetail.Brand, " & _
            "rptPreOrdersDetail.CaseQty" & Chr(13) & "" & Chr(10) & _
            "F", "ROM HJ_TestTBIData.dbo.rptPreOrdersDetail rptPreOrdersDetail" & Chr(13) & "" & Chr(10) & _
        "WHERE (rptPreOrdersDetail.CustNo=29890) AND (rptPreOrdersDetail.DueDate={ts '2010-09-15 00:00:00'}) " & _
            "OR (rptPreOrdersDetail.CustNo=28765) " & _
            "A", "ND (rptPreOrdersDetail.DueDate={ts '2010-09-15 00:00:00'})" & Chr(13) & "" & Chr(10) & _
            "ORDER BY rptPreOrdersDetail.Package, rptPreOrdersDetail.ItemNo"
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_HJPreOrders"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

0
 
LVL 3

Author Comment

by:Lynn Huff
ID: 33704168
That's wrong, let me try again in a minute.
0
 
LVL 3

Author Comment

by:Lynn Huff
ID: 33704201
You were right after all.  I followed only part of your suggestion the first time.  I took out the array, but forgot to take out the commas at the same time.  I'm so used to testing after changing only one item that I forgot those two changes needed to be enacted together.

Thanks.

I'll award points.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Search Text in Views 2 28
Excel Auto-Complete lines 4 32
need count any combinaton of 4 numbers 7 26
Could not use "; file already in use EXCEL 2016 VBA 3 11
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question