I am trying to make an Excel file that will pull from a SQL database and provide the front end user with the desired information. Now excel will do lots of calculations after it has pulled the data over which I have that working just fine. My problem is that everything works great for me on my computer but the second I e-mail it to someone it no longer works because they do not have the same OLEDBConnection as I do. So is there a way for me to create that connection for them. Below is the sample code I have which was just recorded as I did the process.
Sub SQL()
With ActiveWorkbook.Connections("SG_Statday_Player").OLEDBConnection
.BackgroundQuery = False
.CommandType = xlCmdSql
.Connection = _
"OLEDB;Provider=SQLOLEDB.1;Password=****;Persist Security Info=True;User ID=****;Data Source=0.0.0.0;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PCName;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=WINOASIS"
.RefreshOnFileOpen = False
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = ""
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("SG_Statday_Player")
.Name = "SG_Statday_Player"
.Description = ""
End With
With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
Connections("SG_Statday_Player"), Destination:=Range("$A$1")).TableObject
.RowNumbers = False
.PreserveFormatting = True
.RefreshStyle = 1
.AdjustColumnWidth = True
.ListObject.DisplayName = "Table_WINOASIS_Query"
.Refresh
End With
I had thought of trying to write a macro that just adds the connection before it does the Get External Data but the Create connection does not allow for connection to multiple tables where as Get External Data does.
I have now tried copying my connection file to their PC, saving the connection file on the network drive, as well as splitting the data from the two tables I need and pulling them in one table at a time and using VBA to make the connection and all of these have meant with no success.
I have reached out to another forum as I have not seen any response here. Below is the link to that post.
