Excel VBA code for MS Query

[Steff_DK]

Hi

I'm trying to build a roster compiler, that gets MS Query to import
from several different files and sheets.

The user input comes from a calendar object. Whenever I try to change
the below MS Query code from "day2" to "day" & bDay - I get a runtime
error, and VBA highlights the line .Refresh BackgroundQuery:=False

Moreover, the query can't change from sheet to sheet within the same
file, which is also needed as every month has it's own sheet in the
source files.

Also: What does the line "& Chr(13) & "" & Chr(10) &" do in the
code???

Please find below the code I'm working with at the moment.
________________________________________________
Private Sub Calendar1_Click()

Dim bDay As Byte
Dim bMonth As Byte
Dim iYear As Integer
'Defines variable data type

bDay = Calendar1.Day
bMonth = Calendar1.Month
iYear = Calendar1.Year
'Defines variable

Dim sFasource As String
Dim sIcsource As String
Dim sFaasource As String
Dim sTposource As String
Dim sIosource As String
Dim sTkmsource As String
'Defines data type

sFasource = "10-FA-" & iYear & ".xls"
sIcsource = "30-IC-" & iYear & ".xls"
sFaasource = "20-FAA-" & iYear & ".xls"
sTposource = "40-TPO-SM-" & iYear & ".xls"
sIosource = "50-IO-" & iYear & ".xls"
sTkmsource = "60-SO-" & iYear & ".xls"
'Defines source file names

Dim sLabel As String
sLabel = bDay & "_" & bMonth
'Defines source chart label

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Excel-filer;DBQ=Q:\CRC\O-Sek\Tjenestelister\" & _
"sFaSource" &
";DefaultDir=Q:\CRC\O-Sek\Tjenestelister;DriverId=790;MaxBufferSize=204"
_
), Array("8;PageTimeout=5;")), Destination:=Range("F49"))
..CommandText = Array( _
"SELECT day2.O" & Chr(13) & "" & Chr(10) & _
"FROM `Q:\CRC\O-Sek\Tjenestelister\10-FA-2004`.day2 day2")
..Name = "Forespørgsel fra Excel-filer_3"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = True
..RefreshStyle = xlInsertDeleteCells
..SavePassword = True
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..PreserveColumnInfo = True
..Refresh BackgroundQuery:=False
End With
'Query to FA source file

End Sub

 

[Nick Cranham]

Steff,
It looks like the "QueryTables.Add..." part is macro generated, as you have
the "Array(" parts in it. You can drop those to make it easier to read.
"SELECT day2.O" needs to be "SELECT day" & CStr(bDay) & ".O".....
Not sure why there is a "& Chr(13) & "" & Chr(10) &" in the SQL as that's
normally a vbNewLine, which is not good here.

NickHK