VBA excel : export Select SQL query to a worksheet

Hello experts,

I was wondering how can I create a vba procedure in which I report the servername, user information,  database name and Select SQL query and export the query to an specified Sheet.

The idea is to create a generic Procedure in which I include worksheet destination and SQL query as parameter.

This will alllows me to dynamically export the query based on the reported parameters,

Sub exportSQLQuery(Userinfo as String,pswd as String,DBname as String,DestinationWorksheet as Worksheet,SQLquery as ...)

The query should contains the headers of the table selected.

select * from MyTable


Thank you very much for your help.
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

As soon as you have a recordset

see my previous answer
http://www.experts-exchange.com/questions/28708450/VBA-execute-sql-queries.html#a40946199


you can use

    For Idx = 1 To adoRS.Fields.Count
        ActiveSheet.Cells(1, Idx) = adoRS.Fields(Idx - 1).Name
    Next
    ActiveSheet.Range("A2").CopyFromRecordset adoRS

Open in new window

EDIT corrected code
Regards
LD16Author Commented:
Thank you. Where should I put the sql Query? In another sheet in column B as my previous code or I can put directly within the procedure?
Rgonzo1971Commented:
You can put it directly in the procedure
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LD16Author Commented:
Okey,

I have revise the connection string as it wasn't working properly.

Could you please help me to integrate your previous code in order to query out the sql execute based on this procedure. The connections is working properly but I don't know how to out the result in an specific worksheet.

Thank you again for your help.

Sub QueryOut()


Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=Servername;" & _
"UID=Userid;" & _
"PWD=Password;" & _
"Database=DB;"


Dim objRS
Set objRS = CreateObject("ADODB.Recordset")
Dim SQL



SQL = "select  ID, OwningOrganization from mstt_schema.Project where Version = 1"

objRS.Open SQL, objConn

Set rs = objConn.Execute(SQL)

''''''''''''''''''''''''''''''''''''Out the query result

Set objRS = Nothing
Set objConn = Nothing

End Sub
Rgonzo1971Commented:
then try

instead of

''''''''''''''''''''''''''''''''''''Out the query result
this
 
   For Idx = 1 To rs.Fields.Count
        Sheets("YourSheetname").Cells(1, Idx) = rs.Fields(Idx - 1).Name
    Next
    Sheets("YourSheetname").Range("A2").CopyFromRecordset rs

Open in new window

EDIT or
 
   For Idx = 1 To objRS.Fields.Count
        Sheets("YourSheetname").Cells(1, Idx) = objRS.Fields(Idx - 1).Name
    Next
    Sheets("YourSheetname").Range("A2").CopyFromRecordset objRS

Open in new window

LD16Author Commented:
Thank you very much it works:

Here is my revised code:

Sub QueryOut(ws As Worksheet)


Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Driver={SQL Server};" & _
"Server=ServerName" & _
"UID=User;" & _
"PWD=Password;" & _
"Database=db;"


Dim objRS
Set objRS = CreateObject("ADODB.Recordset")
Dim SQL



SQL = "select  ID, OwningOrganization from mstt_schema.Project where Version = 1;"

objRS.Open SQL, objConn

Set rs = objConn.Execute(SQL)


 For Idx = 1 To rs.Fields.Count
        Sheets("Sheet1").Cells(1, Idx) = rs.Fields(Idx - 1).Name
    Next
    
    ws.Range("A2").CopyFromRecordset rs

Set objRS = Nothing
Set objConn = Nothing

End Sub


Sub RunQueryOut()
QueryOut Sheets("Sheet1")
End Sub

Open in new window


I have one question concerning the sqlquery:

If I want to return to the line in the sqlquery:

SQL = "select  ID, OwningOrganization from mstt_schema.Project where Version = 1;"

Replace by :

SQL = "select  ID, OwningOrganization from mstt_schema.Project"  & _  
"where Version = 1;"

Do you know why it doesnt' work?
How can I properly return to the line?

Thank you very much for your help.
Rgonzo1971Commented:
insert a space between the two
LD16Author Commented:
Perfect it works!
I am trying to set up a huge sql query which works in SQL but I don't understand the error message from VBA:

2015-09-02-15_49_38-Microsoft-Visual.png

How can I can properly the query.

Here is the query which works in SQL

SELECT [ActivitySubProject] AS 'Activity sub-project'
      ,P1.value AS 'BRCategory'
      , case [SAPStatus] when 1 then 'LANC' when 2 then 'TCLO' else 'CLO' end AS 'Closing Status'
      ,O.code AS 'OwningOrganization_Code'
      ,[GID]
      ,[GIDDesignation]
      ,[GlobalAccounting]
      ,[InternalStatus] AS 'InternalStatus'
      ,R.name AS 'Location2' 
      ,M.MSTTId AS 'MSTT Id'
      ,S.SPoTId AS 'SPoTProject Id'
      , case [TTStatus] when 0 then 'Closed' when 1 then 'Opened' else 'None' end AS 'TTStatus'
      ,[ProjectTypeSAPCode] AS 'TypeProjetSap'
      ,[CostCenterInSAP] AS 'CostCenterSAP'
      ,[ProfitCenter] AS 'ProfitCenter'
      ,case [HourAuthorized] when 1 then 'YES' else 'NO' end AS 'HourAuthorized'
      ,case [CreateInTT] when 1 then 'YES' else 'NO' end AS 'CreateInTT'
      ,[EOTPType] AS 'EOTPType' 
      ,[DomF] AS 'DomF'
     ,IPT.Value AS 'IFRSProjectType'
	 ,U.SESA AS 'CostCodeManager'
	 ,PD.ProjectDefinition AS 'ProjectDefinition'
	 ,F1.value AS 'Function'
	 ,P2.value AS 'BRCategory2'
	 ,PP1.value AS 'ProjectPowerProgram'
	 ,[Service]
	 ,PD.ProjectLabel AS 'Project Label'

  FROM [Schneider-Workflow-EOTP].[dbo].[GlobalId] G
  left join [Schneider-Workflow-EOTP].[dbo].[parameter] IPT on [IFRSProjectType_Id] = IPT.id
  left join [Schneider-Workflow-EOTP].[dbo].[parameter] P1 on [BRCategory_Id] = P1.id
  left join [Schneider-Workflow-EOTP].[dbo].[parameter] P2 on [BRCategory2_Id] = P2.id
  left join [Schneider-Workflow-EOTP].[dbo].[parameter] F1 on [Function_Id] = F1.id
  left join [Schneider-Workflow-EOTP].[dbo].[parameter] PP1 on [ProjectPowerProgram_Id] = PP1.id
  left join [Schneider-Workflow-EOTP].[dbo].OwningOrganization O on OwningOrganization_Id = O.id
  left join [Schneider-Workflow-EOTP].[dbo].ResourceCenter R on G.GlobalRC_Id = R.id
  left join [Schneider-Workflow-EOTP].[dbo].MSTT M on MSTT_Id = M.id
  left join [Schneider-Workflow-EOTP].[dbo].SPoTProject S on SPoT_id = S.id
  left join [Schneider-Workflow-EOTP].[dbo].[ProjectDefinition] PD on ProjectDefinition_Id = PD.id
  left join [Schneider-Workflow-EOTP].[dbo].[Users] U on CostCodeManager_Id = U.id
  where G.IsDeleted = 0  
  and G.MSTT_Id in (select Id from MSTT where IsDeleted = 0) 
  and G.SPoT_Id in (select ID from SPoTProject where ISDeleted = 0)
  And G.InternalStatus <> 6

Open in new window


Regards,
Rgonzo1971Commented:
the underscore are the line continuations

pls try

strSQL = "Select..."
strSQL = strSQL & " from tblAssets"

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Perfect, I will test it tomorrow, thank  you again for your help!
LD16Author Commented:
Tested and it works!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.