How to automatically pass log on credentials in an Excel report?

[mdebower]

Hi Y'all,

I have an Excel spreadsheet that calls a Windchill report and I want to have it automatically refresh everytime it is opened with no user interaction.   Anyone do this before?

-marc

[mdebower]

I guess I get to answer my own question.  More points for me!! 

After much searching and trial and error I ran across the solution here: http://stackoverflow.com/questions/22149169/how-to-pass-authentication-credentials-in-vba

Place the following code in the ThisWorkbook section in the VBAProject in Excel.  Change to point to your server and also use a valid username and password.

Private Sub Workbook_Open()

With CreateObject("Microsoft.XMLHTTP")
  .Open "GET", "https://your.windchill.server.com/Windchill/app", False, "username", "password"
  .Send
End With

ActiveWorkbook.RefreshAll

End Sub

Note that the username and password will be stored in cleartext in the VBS project.  To mitigate this  create or use an account with read only privileges.

[STEVEG]

Nice Marc.

Will users be given a copy of the excel file for their own use?  Or is this just for your use.  And what about login/password credential security?  Couldn't someone get that information?  The problem with the read-only rights is typically you have to have admin rights to make that change.

[mdebower]

Thanks Steve.

The spreadsheets will actually be used as input into our SQL database that drives an internal webpage.  Not the best way to connect data silos, but you cant beat the price!  So this will limit the exposure to the credential information, because the files will be stored on our web server with very limited user access.

The other thing I did to mitigate the exposure is I created a special user account just to use for these reports,  we already have 6 or 7 and I can see it growing in the future so it made sense to create an account.  This report maker account is limited to view only or Guest privileges.  If someone was to gain access, they could still look around and in some contexts download files, but couldn't make any changes.  Again, not the best solution, but workable.

If there are better ways to implement this, I would love to hear them...   Always open to better ways of doing things.

[STEVEG]

You put some thought into it.  I'm no security export expert and I don't know of any better way.  I just wanted to bring up some thoughts.

Edit: I just can't type and proofread lately.