Open new workbook

[dboone25]

At the moment running an SQL query that outputs data to an active sheet.

What I would like to do is when the CommandButton is pressed, instead of pasting the dataset to the ActiveWorkbook to set it to the New Work Book that I have added but I dont know how to set this...

Private Sub CommandButton1_Click()        'open new workbook
        With CreateObject("Excel.Application")
            .Workbooks.Add
            .Visible = True
        End With
        
        'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset


        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset


        'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=NP-DATABASE;Initial Catalog=" & ComboBox1.Value & " ;Integrated Security=SSPI;"


        objMyConn.ConnectionTimeout = 0
        objMyConn.CommandTimeout = 0


        objMyConn.Open


        'Set and Excecute SQL Command from TextBox'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = TextBox1.Value  ' query run from TextBox
                              
        objMyCmd.CommandType = adCmdText


        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open
        
        'include headers from recordset
       With objMyRecordset
        For i = 1 To .Fields.Count
            ActiveSheet.Cells(1, i) = .Fields(i - 1).Name
        Next i
        
       End With


        'Copy Data to Excel'
        ActiveSheet.Range("A2").CopyFromRecordset objMyRecordset


End Sub

Is there a way where once the new work book opens the data that the SQl query has run can be placed into the new workbook and not the ActiveSheet as I have it setup?

 

[MrKowz]

Try:

Private Sub CommandButton1_Click()        'open new workbook
        [COLOR="#FF0000"][B]Dim wb As Workbook[/B][/COLOR]
        With CreateObject("Excel.Application")
            .Workbooks.Add
            .Visible = True
        End With
        
        'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset


        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset


        'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=NP-DATABASE;Initial Catalog=" & ComboBox1.Value & " ;Integrated Security=SSPI;"


        objMyConn.ConnectionTimeout = 0
        objMyConn.CommandTimeout = 0


        objMyConn.Open


        'Set and Excecute SQL Command from TextBox'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = TextBox1.Value  ' query run from TextBox
                              
        objMyCmd.CommandType = adCmdText


        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open
        
        'include headers from recordset
       With objMyRecordset
        For i = 1 To .Fields.Count
            ActiveSheet.Cells(1, i) = .Fields(i - 1).Name
        Next i
        
       End With

        [B][COLOR="#FF0000"]Set wb = Workbooks.Add[/COLOR][/B]

        'Copy Data to Excel'
        [B][COLOR="#FF0000"]wb[/COLOR][/B].Range("A2").CopyFromRecordset objMyRecordset


End Sub

 

[Roxxien]

I'm not that familiar with connection like that in VBA but I might have a way for you.

By nature, if no workbook or sheets is specified, VBA use the "active" one.

In that way, you can either "select" the new workbook or specify the workbook that you want your data on.

Workbook("Book1").select

Workbook("Book1").objMyConn.ConnectionString = 'Well a normal specification

As I am clearly not sure, please try this in a copy of your file.

Edit, MrKowz made that even more clear while I was writing

 

[dboone25]

Good morning, many thanks for getting back to me so quick!

Unfortunately I get a run-time error ' Object does not support this property method.

This includes adding wb into

wb.Cells(1, i) = .Fields(I - 1).Name

That was to include the headers from the dataset.

Any ideas what im missing?

 

[Norie]

Try this.

Private Sub CommandButton1_Click()        
        'Declare variables'
Dim objMyConn As ADODB.Connection
Dim objMyCmd As ADODB.Command
Dim objMyRecordset As ADODB.Recordset
Dim wbNew As Workbook
Dim wsNew As Worksheet 

       'open new workbook
        
        Set wbNew = Workbooks.Add
        Set wsNew = wbNew.Sheets(1)

        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

        'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=NP-DATABASE;Initial Catalog=" & ComboBox1.Value & " ;Integrated Security=SSPI;"

        objMyConn.ConnectionTimeout = 0
        objMyConn.CommandTimeout = 0

        objMyConn.Open

        'Set and Excecute SQL Command from TextBox'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = TextBox1.Value  ' query run from TextBox
                              
        objMyCmd.CommandType = adCmdText

        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open
        
        'include headers from recordset
        With objMyRecordset
             For i = 1 To .Fields.Count
                 wsNew.Cells(1, i) = .Fields(i - 1).Name
             Next i  
        End With

        'Copy Data to Excel'
        wsNew.Range("A2").CopyFromRecordset objMyRecordset


End Sub

 

[dboone25]

Thanks Roxxien, I tried this but comes with Complie error

Sub or function not defined.

Ive tried declaring the Workbook as 'Dim Workbook As Workbook'.

Would I need to keep

With CreateObject("Excel.Application")
.Workbooks.Add
.Visible = True
End With

 

[Norie]

You do not need this.

With CreateObject("Excel.Application")
    .Workbooks.Add
    .Visible = True
End With

That code will create a new instance of Excel and then create a new workbook in that instance.

 

[dboone25]

Brilliant!, thank you again Norie..you're a star!! fantastic