A
Android
I had posted this in microsoft.public.excel.querydao, but there does not
seem to be much activity there. So re-posting here.....
This is my 1st attempt to use a SQL query via VBA to extract data from an
Access db and copy it to an Excel sheet. Having some problems.
Using the excellent info in
http://www.dicks-clicks.com/excel/ExternalData3.htm#CreateVBA as a guide, I
have my code as follows:
1) When the Excel file is opened, the Public Sub Workbook_Open()
subroutine runs and
- Calls a Sub to create the DBQ parameter and the DefaultDir parameters.
This works fine
- When it Tries to run the next statement, "Call CreateQT", it gives a
compile error that: Sub or Function not defined
Could someone please explain. I have attached the "Public Sub CreateQT()"
sub (partly reproduced below) to the "LinkedLineData" sheet where I want the
extracted data copied to (I understand this is required).
2) The code extract is:
Public Sub CreateQT()
Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable
sConn = "ODBC;DSN=MS Access Database;"
sConn = sConn & "DBQ=" & ReportFileName & ";"
sConn = sConn & "DefaultDir=" & DataFileDirectory & ";"
sConn = sConn & "DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"
sSql = "SELECT "
sSql = sSql & "`Line Report`.datetime, "
sSql = sSql & "`Line Report`.groupNumber, "
:
:
sSql = sSql & "`Line Report`.totalLineBusyTime"
sSql = sSql & "FROM `" & DataFileDirectory & "\" & ReportFileName &
"`.`Line Report` `Line Report`"
sSql = sSql & "ORDER BY `Line Report`.datetime, `Line
Report`.groupNumber"
Set oQt = ThisWorkbook.Sheets("LinkedLineData").QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("a1"), _
Sql:=sSql)
oQt.Refresh
End Sub
seem to be much activity there. So re-posting here.....
This is my 1st attempt to use a SQL query via VBA to extract data from an
Access db and copy it to an Excel sheet. Having some problems.
Using the excellent info in
http://www.dicks-clicks.com/excel/ExternalData3.htm#CreateVBA as a guide, I
have my code as follows:
1) When the Excel file is opened, the Public Sub Workbook_Open()
subroutine runs and
- Calls a Sub to create the DBQ parameter and the DefaultDir parameters.
This works fine
- When it Tries to run the next statement, "Call CreateQT", it gives a
compile error that: Sub or Function not defined
Could someone please explain. I have attached the "Public Sub CreateQT()"
sub (partly reproduced below) to the "LinkedLineData" sheet where I want the
extracted data copied to (I understand this is required).
2) The code extract is:
Public Sub CreateQT()
Dim sConn As String
Dim sSql As String
Dim oQt As QueryTable
sConn = "ODBC;DSN=MS Access Database;"
sConn = sConn & "DBQ=" & ReportFileName & ";"
sConn = sConn & "DefaultDir=" & DataFileDirectory & ";"
sConn = sConn & "DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"
sSql = "SELECT "
sSql = sSql & "`Line Report`.datetime, "
sSql = sSql & "`Line Report`.groupNumber, "
:
:
sSql = sSql & "`Line Report`.totalLineBusyTime"
sSql = sSql & "FROM `" & DataFileDirectory & "\" & ReportFileName &
"`.`Line Report` `Line Report`"
sSql = sSql & "ORDER BY `Line Report`.datetime, `Line
Report`.groupNumber"
Set oQt = ThisWorkbook.Sheets("LinkedLineData").QueryTables.Add( _
Connection:=sConn, _
Destination:=Range("a1"), _
Sql:=sSql)
oQt.Refresh
End Sub