Solved

EXcel 2007 Refreshall using VBA and connection string

Posted on 2010-11-07
18
2,510 Views
Last Modified: 2012-05-10
Hi, I have a few Excel spreadsheets that are connected with the 2007 Data Connection using Sql Authentication (SQL 2005).  I automated the refreshall with no problem, but the connection form pops up to insert a UID and password.  This process will be kicked off using SSIS so I need to automaticly pass in the correct connection string.  I can't change to Windows authetication.  The excel spreadseeht is saved as a .xlsx which removes the VBA so that our users will not have access.  I have been trying to figure out how to do prevent the connection pop up form from opening and pass in the VBA connection string but have not been able to come up with the correct VBA code or find what I need to perform a Refresh All.  The Refresh ALL runs a proc in SQL that updates a few worksheets in the workbook.  In most instances they are Pivot tables, but in some, it is straight data - columns and rows.  Any help would be appreciated.

Thanks
0
Comment
Question by:DMUM
  • 9
  • 8
18 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34082916
I'm not clear on how the refresh is to be triggered. You write that it will be done from SSIS, but the question says your looking for how to do this using VBA?
0
 

Author Comment

by:DMUM
ID: 34084808
Hi, I have written a Script in SSIS to open and refresh the excel spreadsheet.  This will be ran everynight to referesh the data in the Excel spreadsheet which is a report for our users and we do not allow them access to do refreshes on our system.  The SSIS works fine, but Excel will ask me for the password to connect to our sql database.  This is fine if I'm watching it, but the point is to not have to.  We want to fully automate.  Thanks
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34085119
I'm sorry to say I know too little about SSIS to help you here.
0
 

Author Comment

by:DMUM
ID: 34085141
I don't need anything answered for SSIS.  I need to know for Excel VBA.  I need to stop the password pop up form from popping up by giving the RefreshALL a connection string, but I do not know how to go about it.  SSIS only kicks off the VBA code.  I have no questions for SSIS.  Sorry for the confusion.
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34085938
Ah. I would recod a macro whilst creating a connection where you tell Excel to keep the password. That should get you a bit of code including the connection string. I would also record one NOT keeping password. That way you get both strings. Then you can use the connection property of your connections and update it to the one with PW, do the update and then change the conn string back to the one without PW.
0
 

Author Comment

by:DMUM
ID: 34086062
Hi, unfortunately it isn't as simple as that - believe me, that was my first try:).  The only thing it records is ActiveWorkBook.RefreshAll.  It doesn't do anything with the connection string.  Any other insight?  I know the connection string, I just don't know how to apply it so that when it does do the ActiveWorkbook.Refreshall it uses the connection string.  I tried ActiveWorkbook.Refreshall("Connection").  Connection is the name of my connection string, but I get an error that says the syntax is not supported
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34090471
I would set up all connections with a connection string including the Username/PW, similar to this one:

Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;

Then do the refresh and finally, replace the connection strings with strings w.o. username/pw and then save-as the file to xlsx. I expect that should do it.
0
 

Author Comment

by:DMUM
ID: 34094000
Hi, Sorry, I don't follow....

So in VBA,

1. create the connection string
2. run the refresh with the VBA
3. change the data connection pop up form to not shoe username/pw
4. save file as .xlsx

How id this going to prevent the pop up?  I already save the after refresh file as an xlsx to remove the VBA module, however, this does nothing to automating the process without having to enter the password and userid for the pop up.  

As I said earlier, I already have the connection string, but how do I associate the connection string with the
Activeworkbooks.RefreshAll..this line causes the popup to open to enter your password before proceeding.

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 11

Expert Comment

by:jkpieterse
ID: 34094134
I can't do any testing, as I don't have any sql server databases available which use Username/pw authentication.

Are you saying you can't get Excel to remember he username and password in the connections?
0
 

Author Comment

by:DMUM
ID: 34094172
no

I'm saying I can't pass the connectin string that I've created in VBA to the ActiveWorkbooks.RefreshALL string in VBA
I don't know the correct syntax
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34095045
Like this:
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34095052
Somehow the code snippet disappeared.
Public Function UpdateConnections()
    Dim sCon As String
    Dim sPath As String
    Dim oPc As PivotCache
    sPath = ThisWorkbook.Path
        sCon = "YourConnection"
        Worksheets(1).QueryTables(1).Connection = sCon
        For Each oPc In ThisWorkbook.PivotCaches
            On Error Resume Next
            oPc.Connection = sCon
        Next
        UpdateConnections = True
End Function
0
 

Author Comment

by:DMUM
ID: 34095784
so you can' do refreshALL.  You have to refresh the pivot tables.  By the way, tried your code - thank you, but it deosn't work.  It stops on the line Worksheets(1).QueryTables(1).Connection = sCon

I don' have any queryTables in my Excel workbook.  The data comes from SQL using the DataConnection menu item in Excel2007.  It runs a stored proc and refreshes the data in all the Excel worksheets.
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 34100769
The code was intended as example code, showing how to just redo the connections. After redoing the connections, you still have to do the refreshall.

I wasn't sure whether you had any querytables, so I just left that statement in there for illustration on how to do those.
After doing the refresh, you can re-run the updateconnections routine with a connection string without password. Otherwise the password may be hard-wired in your file, which isn't very safe.
0
 

Author Comment

by:DMUM
ID: 34131993
Sorry for delay in responding. Working overtime preparing for vacation....anyway, not quite clear on why I would need to run the refresh again to remove password if I am using vba to control the refresh.  Can you please explain? I think you may not completly understand. I do not want to disconnect the data connection completly on the ribbon and just uxe vba to perfrom the refresh. I will be password protecting the vba module so the user will never see the connection string.  Thanks
0
 
LVL 11

Accepted Solution

by:
jkpieterse earned 50 total points
ID: 34133752
My idea was:
- Update all tables using the username and password in the connection string so you're not prompted for them (but I can't test whether this actually works, since I have no database requiring logon)
- After refreshing, remove the credentials from all connect strings.
Why remove? In Excel 2007 it is very easy to look at your connection strings. Even in 2003, you simpy do something like:
?ThisWorkbook.PivotCaches(1).Connection
in the immediate window to uncover your uid and pw to the database.
0
 

Author Closing Comment

by:DMUM
ID: 34398489
I appreciated the attempt to help, but since the person could not try out the code he was providing me, it didn't really help.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now