When I copy the code into my macro the following part highlights as Compile
Error:Syntax Error.
.CommandText = Array(, "SELECT * " & _
"FROM dms_reporting.dms_uk.A_Customer_Month_End_View_UK
A_Customer_Month_End_View_UK " & _
"WHERE A_Customer_Month_End_View_UK.""Account Number"" = """ &
AccountNumber & """)
If I remove one of the " before '& AccountNumber &' it eliminates the error
message. Not sure if this is correct but it seemed logical due to the odd
number of " in the sequence.
Unfortunately when the Macro runs it stops at the same point showing Type
mismatch error 13.
"joel" wrote:
This is much too complicated to tackle all at once. The code below is
returning all the columns from the table (Select *) and filtering on 1
parameter the AccountNumber. If this works we will add a little bit
more each time. I'm using Sheet1 which you can change as needed and I
made the AccountNumber = "Customer NBR"
VBA Code:
--------------------
Sub Edit_DMS_Query()
'
' Edit_DMS_Query Macro
' Macro recorded 27/04/2010 by markbyfo
'
' Keyboard Shortcut: Ctrl+e
'
Set Sht = sheets("sheet1")
AccountNumber = "Customer NBR"
with Sht
.cells.RemoveSubtotal
with .QueryTables.Add(Connection:= "ODBC;DRIVER=SQLServer;" & _
"SERVER=usoldt-as-056;UID=dms_uk;;" & _
"APP=Microsoft Office 2003;WSID=UKEDG-L41655;" & _
"DATABASE=dms_reporting", _
Destination:=.Range("A5"))
.CommandText = Array(, "SELECT * " & _
"FROM dms_reporting.dms_uk.A_Customer_Month_End_View_UK A_Customer_Month_End_View_UK " & _
"WHERE A_Customer_Month_End_View_UK.""Account Number"" =""" & AccountNumber & """)
.Name = "Query from DMS UK Production Month End"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
end with
End With
End Sub
--------------------
--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198559
http://www.thecodecage.com/forumz
.