Creating a connection to Analysis Services

I'm trying to learn how to create a connection via VBA to a external Financial Analysis database where I then can create a Pivot Table and run CUBE formulas from that connection.

Currently, If I want to create a connection, I need to enter

1. the url address. something like. https://someaddress/blah/blah.dll
2. A username and password.

I know after I do this, the connection creates an odc file and the connection is stored on the PC. After that I typically just tell Excel to create the connection only, and then I will add pivot table if needed and start building a spreadsheet with the CUBE formulas.

I'm trying to perhaps make this easier for others where I may look to include the script in our XLAM add in file where the user would maybe be prompted for the password in creating the connection. Also, is there any to store the connection in the XLAM Add-in file itself and not in an odc file? Does that make sense?

I'm just not sure even where to start in trying to create the connection.

If it helps, I did export the odc file and can see this information. So I think I just need to learn how to turn into a script.

<odc:Connection odc:Type="OLEDB">
   <odc:ConnectionString>Provider=MSOLAP.8;Password=MYPASSWORD;Persist Security Info=True;User ID=MYUSERID;Data Source=https://sourcepath.dll;Update Isolation Level=2;Initial Catalog=MYUSERNAME</odc:ConnectionString>
   <odc:CommandType>Cube</odc:CommandType>
   <odc:CommandText>FILENAME</odc:CommandText>
   <odc:CredentialsMethod>None</odc:CredentialsMethod>
  </odc:Connection>
 </odc:OfficeDataConnection>

Any help is appreciated.

Thanks,
PT