Excel and VB

[Venedict]

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

 

[Bob Phillips]

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