[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2666
  • Last Modified:

EXcel 2007 Refreshall using VBA and connection string

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
DMUM
Asked:
DMUM
  • 9
  • 8
1 Solution
 
jkpieterseCommented:
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
 
DMUMAuthor Commented:
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
 
jkpieterseCommented:
I'm sorry to say I know too little about SSIS to help you here.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DMUMAuthor Commented:
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
 
jkpieterseCommented:
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
 
DMUMAuthor Commented:
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
 
jkpieterseCommented:
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
 
DMUMAuthor Commented:
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
 
jkpieterseCommented:
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
 
DMUMAuthor Commented:
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
 
jkpieterseCommented:
Like this:
0
 
jkpieterseCommented:
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
 
DMUMAuthor Commented:
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
 
jkpieterseCommented:
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
 
DMUMAuthor Commented:
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
 
jkpieterseCommented:
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
 
DMUMAuthor Commented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now