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;DictionaryMo de=0;StandardMode=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 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. Any improvements are also welcome.