I am a novice at programming and I am sure there may be more efficient
ways to accomplish the same task but I am attempting to emulate a
recorded macro in Excel and substitute variables for the folder path
(blue) and the file name (green). The recorded macro is as follows:

Sub Macro1

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=Timberline Estimating Data;DBQ=G:
\Illigdbase1;CODEPAGE=1252;DictionaryMode=0;Standa rdMode=0;MaxColSupport=255;ShortenNames="
_
), Array("0;DatabaseType=2;")), Destination:=Range("A1"))
.CommandText = Array( "SELECT * FROM ""308 NORTH RODEO DR
GC2_PEE_ESTIMATE_COVERPG_SETUP""")
.Refresh BackgroundQuery:=False
End With

End Sub

I have successfully created functions that provide the folder path
(FolderFromPath) and the file name (GetEstimateName).

Sub Macro2

GetEstimateName
FolderFromPath (strFullPath)
CoverpageQuery = GetEstimateName & "_PEE_ESTIMATE_COVERPG_SETUP"

' Connection String
ConnString = "ODBC;DSN=Timberline Estimating
Data;DBQ=FolderFromPath;" '

' Query String
QueryString = "SELECT * FROM CoverpageQuery"

With
ActiveSheet.QueryTables.Add(Connection:=Array(Arra y(ConnString),
Array("0;DatabaseType=2;")), Destination:=Range("A1"))
.CommandText = QueryString
.Refresh BackgroundQuery:=False
End With
End Sub

I seem to be having trouble with the connection and query strings and
passing them to the With statement. The FolderFromPath variable does
not to work in the ConnString. The CoverpageQuery is passed to the
QueryString with single quotes. I'm not sure how to accomodate the
double quotes. Any help or suggestions would be appreciated.

schreef in bericht
...
I am a novice at programming and I am sure there may be more efficient
ways to accomplish the same task but I am attempting to emulate a
recorded macro in Excel and substitute variables for the folder path
(blue) and the file name (green). The recorded macro is as follows:

Sub Macro2

GetEstimateName
FolderFromPath (strFullPath)
CoverpageQuery = GetEstimateName & "_PEE_ESTIMATE_COVERPG_SETUP"

' Connection String
ConnString = "ODBC;DSN=Timberline Estimating
Data;DBQ=FolderFromPath;" '

' Query String
QueryString = "SELECT * FROM CoverpageQuery"

With
ActiveSheet.QueryTables.Add(Connection:=Array(Arra y(ConnString),
Array("0;DatabaseType=2;")), Destination:=Range("A1"))
.CommandText = QueryString
.Refresh BackgroundQuery:=False
End With
End Sub



I think you should try it like this:

'Connection String
ConnString = "ODBC;DSN=Timberline Estimating
Data;DBQ=" & FolderFromPath

On Jan 10, 9:52*am, "m00n" wrote:
schreef in ...





I am a novice at programming and I am sure there may be more efficient
ways to accomplish the same task but I am attempting to emulate a
recorded macro in Excel and substitute variables for the folder path
(blue) and the file name (green). The recorded macro is as follows:

Sub Macro2

* *GetEstimateName
* *FolderFromPath (strFullPath)
* *CoverpageQuery = GetEstimateName & "_PEE_ESTIMATE_COVERPG_SETUP"

' * Connection String
* *ConnString = "ODBC;DSN=Timberline Estimating
Data;DBQ=FolderFromPath;" '

' * Query String
* *QueryString = "SELECT * FROM CoverpageQuery"

* *With
ActiveSheet.QueryTables.Add(Connection:=Array(Arra y(ConnString),
Array("0;DatabaseType=2;")), Destination:=Range("A1"))
* * * *.CommandText = QueryString
* * * *.Refresh BackgroundQuery:=False
* *End With
End Sub

I think you should try it like this:

'Connection String
* * ConnString = "ODBC;DSN=Timberline Estimating
Data;DBQ=" & FolderFromPath- Hide quoted text -

- Show quoted text -

Would you have any suggestions for the double quotes in the
QueryString or Coverpagequery