VBA excel : export Select SQL query to a worksheet

Luis Diaz
Luis Diaz used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
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
Luis DiazIT consultant

Author

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?
Top Expert 2016

Commented:
You can put it directly in the procedure
Ensure you’re charging the right price for your IT

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

Luis DiazIT consultant

Author

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
Top Expert 2016
Commented:
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

Luis DiazIT consultant

Author

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.
Top Expert 2016

Commented:
insert a space between the two
Luis DiazIT consultant

Author

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,
Top Expert 2016
Commented:
the underscore are the line continuations

pls try

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

Open in new window

Regards
Luis DiazIT consultant

Author

Commented:
Perfect, I will test it tomorrow, thank  you again for your help!
Luis DiazIT consultant

Author

Commented:
Tested and it works!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial