change code to be literal

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Hello Everyone,

I would like to change this code to specifically get a month of code let's say June.

Code:
Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Dim Month$
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
        "SELECT Apps FROM track apps Where Month = June"
            'Month = InputBox("Please input the Month" & _
            '"(for example, 'August').")
    comm.Parameters(0) = ""
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub
I also want to take out the input box.

Any suggestions?

TIA

Kurt
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
The code works now

Hello Norie,

Again a BIG THANKS! The code works. I just need to reference the server now which I know need to be placed in the Data Source part of this line:

Code:
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source= CHECNET\NEO3" + ThisWorkbook.Path + "\From Dyer.mdb;"

I will let you know the outcome. However the good news is the SQL syntax is now correct!

Have a great day!

Kurt
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
not a valid file name

Hello Norie,

What is the naming convetion for the Data Source??

Code:
Sub get_applications()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source= \CHECSQLDEV1\NEO3" + ThisWorkbook.Path + "\From Dyer.mdb;"
    Set comm.ActiveConnection = conn
    strSQL = "TRANSFORM Sum([track apps].Apps) AS SumOfApps "
    strSQL = strSQL & "SELECT dbo_SMT_Branches.BranchTranType "
    strSQL = strSQL & "FROM [track apps] LEFT JOIN dbo_SMT_Branches ON [track apps].Branch = dbo_SMT_Branches.BranchNbr "
    strSQL = strSQL & "GROUP BY dbo_SMT_Branches.BranchTranType "
    strSQL = strSQL & "PIVOT [track apps].MONTH;"
    comm.CommandText = strSQL
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

Thanks,

Kurt
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
Kurt

That's not something I know, I'm not particularly familiar with ADO, which is what you appear to be using.

If you are just wondering how to refer to a mapped drive then perhaps you just need to use the UNC address for it.

Perhaps if you recorded a macro when you imported the query via Data>Get External Data... you might get some pointers.
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Again a big thanks

Hello Norie,

BIG THANKS!

I am almost there.

I am trying your suggestions.

Have a very good day!

Kurt
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Can anyone else clue me in

Hello All,

This is the last part of the puzzle:

Code:
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source= CHECSQLDEV1\NEO3\CHEC\knichols" + ThisWorkbook.Path + "\From Dyer.mdb;"

What am I doing wrong here? Yes I looked at our server paths per Norie's suggestion.

TIA

Kurt
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,305
Office Version
  1. 365
Platform
  1. Windows
Kurt

What error/problem are you encountering?

Did you try my suggestion of recording a macro?
 

Forum statistics

Threads
1,143,655
Messages
5,720,112
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top