DMUM
asked on
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
Thanks
Jan Karel Pieterse
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?
ASKER
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
I'm sorry to say I know too little about SSIS to help you here.
ASKER
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.
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.
ASKER
Hi, unfortunately it isn't as simple as that - believe me, that was my first try:). The only thing it records is ActiveWorkBook.RefreshAll.
I would set up all connections with a connection string including the Username/PW, similar to this one:
Server=myServerAddress;Dat
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.
Server=myServerAddress;Dat
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.
ASKER
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
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
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?
Are you saying you can't get Excel to remember he username and password in the connections?
ASKER
no
I'm saying I can't pass the connectin string that I've created in VBA to the ActiveWorkbooks.RefreshALL
I don't know the correct syntax
I'm saying I can't pass the connectin string that I've created in VBA to the ActiveWorkbooks.RefreshALL
I don't know the correct syntax
Like this:
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(
For Each oPc In ThisWorkbook.PivotCaches
On Error Resume Next
oPc.Connection = sCon
Next
UpdateConnections = True
End Function
Public Function UpdateConnections()
Dim sCon As String
Dim sPath As String
Dim oPc As PivotCache
sPath = ThisWorkbook.Path
sCon = "YourConnection"
Worksheets(1).QueryTables(
For Each oPc In ThisWorkbook.PivotCaches
On Error Resume Next
oPc.Connection = sCon
Next
UpdateConnections = True
End Function
ASKER
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(
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.
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.
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.
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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.