Problems with using SQL via VBA to extract db data and copy to a worksheet
 

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/Ex....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

Problems with using SQL via VBA to extract db data and copy to a worksheet
 

Put the CreateQT procedure into a normal code module and just make sure
that the worksheet where the data is supposed to go is active before you
call it by doing something like this:

Worksheets("LinkedLineData").Activate
Call CreateQT

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Android" wrote in message
...
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/Ex....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