PDA

View Full Version : Sleeper: Connecting to SQL



brorick
11-10-2004, 01:50 PM
I am trying to use the following code to connect my Excel spreadsheet to an ODBC SQL connection. I know the connection is working, but, I am having a problem with the .CommandText = Array section. Any recommendations is greatly appreciated.:rolleyes:



Sub sqlconn()
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=BOPP;Description=BOPP;UID=BOPP;PWD=bo$$;APP=Microsoft Office XP;WSID=FA287223" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT tblBOPP_Projects.RequestID, tblBOPP_Projects.ProjectID, tblBOPP_Projects.WorkplaceMgrID, tblBOPP_Projects.Name, tblBOPP_Projects.Description, tblBOPP_Projects.ProductivityImpact, tblBOPP_Projec" _, _
"ts.Status, tblBOPP_Projects.Notes, tblBOPP_Projects.FDPriority, tblBOPP_Projects.ProjectType, tblBOPP_Projects.LeadAnalyst, tblBOPP_Projects.ALPSAnalyst, tblBOPP_Projects.ALPSIntAnalyst, tblBOPP_Proje" _, _
"cts.AppDevAnalyst, tblBOPP_Projects.BSAnalyst, tblBOPP_Projects.CallAnalyst, tblBOPP_Projects.DMAnalyst, tblBOPP_Projects.TSAnalyst, tblBOPP_Projects.WebAnalyst, tblBOPP_Projects.WFAnalyst, tblBOPP_Pr" _, _
"ojects.Requester, tblBOPP_Projects.Sponsor, tblBOPP_Projects.DecisionMaker, tblBOPP_Projects.DeptMgrName, tblBOPP_Projects.CustomerRep, tblBOPP_Projects.EstimatedHours, tblBOPP_Projects.DateReceived, " _, _
"tblBOPP_Projects.TargetCompletion, tblBOPP_Projects.DateCompleted, tblBOPP_Projects.CompSignOffDate, tblBOPP_Projects.""Date Assigned"", tblBOPP_Projects.Affected_Department, tblBOPP_Projects.Affected_D" _
,,)
.Name = "Query from BOPP"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Juan Pablo Gonz?lez
11-10-2004, 03:33 PM
Don't break it into an array, just use a string


.CommandText = "SELECT ......"

brorick
11-11-2004, 06:44 AM
Thank you for your help.