B
Billy
Hi,
When I come back to work today, I saw your replay. I am so happy that you
can help me.
Here's the code.
Sub t3()
'
'
Range("a2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access
Database;DBQ=C:\db2.mdb;DefaultDir=C:;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Workbooks("test1.xls").Sheets("Sheet1").Range("B6"))
.CommandText = Array( _
"SELECT Sheet1.ID, Sheet1.`PR NUMBER`, Sheet1.DATE, Sheet1.`Number
1`, Sheet1.`Time Spend`" & Chr(13) & "" & Chr(10) & "FROM `C:\db2`.Sheet1
Sheet1" & Chr(13) & "" & Chr(10) & "WHERE (Sheet1.`PR NUMBER`=3.0)" _
)
.Name = "ExternalData_1"
.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
End Sub
Regards,
Billy
----- Original Message -----
From: "onedaywhen" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Friday, November 28, 2003 12:48 AM
Subject: Re: Write data to access file through EXCEL
When I come back to work today, I saw your replay. I am so happy that you
can help me.
Here's the code.
Sub t3()
'
'
Range("a2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access
Database;DBQ=C:\db2.mdb;DefaultDir=C:;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Workbooks("test1.xls").Sheets("Sheet1").Range("B6"))
.CommandText = Array( _
"SELECT Sheet1.ID, Sheet1.`PR NUMBER`, Sheet1.DATE, Sheet1.`Number
1`, Sheet1.`Time Spend`" & Chr(13) & "" & Chr(10) & "FROM `C:\db2`.Sheet1
Sheet1" & Chr(13) & "" & Chr(10) & "WHERE (Sheet1.`PR NUMBER`=3.0)" _
)
.Name = "ExternalData_1"
.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
End Sub
Regards,
Billy
----- Original Message -----
From: "onedaywhen" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Friday, November 28, 2003 12:48 AM
Subject: Re: Write data to access file through EXCEL