Edit: Sorry, wrong thread, can somebody move to programming please?
Looking for a little help
here. Basically what I need to do is assign a macro to a button I create in VBA code. Ultimately what is happening in my
I need a simple way for our program assistant to generate certain data in a fixed way so she can do a
mail merge later on. The data she needs is stored in a SQL database. What I want to do is pull a list of SessionID's for her
in column A, in column B create a Button that she can click and when clicked with create the excel document that she can use
in her mail merge.
I have the SQL code done(for both sections, just showing part 1 now), now I just need to get
my button to work. So far this is what I have.
Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim query As String
Dim row As Integer
Dim btn As Button
Dim t As Range
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
'The database in this instance has been set as Northwind.
'you will need to update the database for what yours is called.
'The IP Address '127.0.0.1' represents localhost. If you
'are trying to connect to a remote sql server then you will
'either need to enter the ip address or URL of that server.
'In the connection string below, 1433 is the port number
'the SQL server is listening on. If your sql server is
'listening on a different port you'll have to change it.
'1433 is the default port for SQL Server.
cs = "DRIVER=SQL Server;"
cs = cs & "DATABASE=***;"
cs = cs & "SERVER=***"
'parameters here are connectionSring, username, password
'you will need to put the actual username and password in
'quotes here for this code to work.
conn.Open cs, "***", "***"
query = "SELECT SessionNumber FROM cerSession WHERE RowStatus = 'A' ORDER BY SessionNumber DESC"
rs.Open query, conn
row = 0
Do Until rs.EOF
row = row + 1
Cells(row, 1).Value = rs.Fields("SessionNumber").Value
Set t = ActiveSheet.Range(Cells(row, 2), Cells(row, 2))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
.Caption = "Generate"
.Name = rs.Fields("SessionNumber").Value
.OnAction = "btnS"
'If rs.State = adStateOpen Then
' Set rs = Nothing
Set conn = Nothing
The above code populates column A with the SessionID and column B with a button with the title 'Generate' however when I
click the button I get this following error:
"Cannot run the macro 'Book2.xlsm!btnS'. The macro may not be
available in this workbook or all macros may be disabled."
even though the btnS call at the end of my code
works. Perhaps I am looking at this all wrong but any help would be greatly appreciated. I've spent a few hours scouring
Google but nothing has helped so far.