Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB
Hi All,
I'm new in excel pivot table. I have a client request to write an interface in VB to call and refresh a pivot table in excel. Currently the excel file contents is using Microsoft Query with ODBC to access the SQL server to retrieve data. This excel file need to deploy to multiple site. Due to the databasename is diff for each site, every time they deploy to another site, they need to modify the excel file. My question is can i use VB to modify the Microsoft Query statement to point to correct servername and database? If possible, anyone have sample code? Any advice is much appreciated.Thanks. Regards, Venedict |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB
Venedict,
You could try setting an environment variable to point at the correct database and retrieve that in VB Either myDB = Environ("dbName") or an API Private Declare Function GetEnvironmentVariable Lib "kernel32" _ Alias "GetEnvironmentVariableA" _ (ByVal lpName As String, _ ByVal lpBuffer As String, _ ByVal nSize As Long) As Long Private Declare Function SetEnvironmentVariable Lib "kernel32" _ Alias "SetEnvironmentVariableA" _ (ByVal lpName As String, _ ByVal lpValue As String) As Long Sub xx() MsgBox GetEnvironmentVar("dbName") End Sub Function GetEnvironmentVar(Name As String) As String GetEnvironmentVar = String(255, 0) GetEnvironmentVariable Name, GetEnvironmentVar, Len(GetEnvironmentVar) GetEnvironmentVar = TrimNull(GetEnvironmentVar) End Function Private Function TrimNull(item As String) Dim iPos As Long iPos = InStr(item, vbNullChar) TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item) End Function -- HTH RP "Venedict" wrote in message ... Hi All, I'm new in excel pivot table. I have a client request to write an interface in VB to call and refresh a pivot table in excel. Currently the excel file contents is using Microsoft Query with ODBC to access the SQL server to retrieve data. This excel file need to deploy to multiple site. Due to the databasename is diff for each site, every time they deploy to another site, they need to modify the excel file. My question is can i use VB to modify the Microsoft Query statement to point to correct servername and database? If possible, anyone have sample code? Any advice is much appreciated.Thanks. Regards, Venedict |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|