D
drinese18
Does anyone know of a way to download data directly into a certain column?
Basically at the moment I wrote some code to download data from an SQL server
in which it queries the data when downloading. After it has finished
downloading on that page I would use VLOOKUPS to get the data from that page
but I now want to eliminate that extra procedure and have it download
directly onto the page in a single column, my code can be seen below:
Sub SPICEdownload_query()
On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("SHEETNAME")
Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
Dim ts As String
Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=DSN;UID=UID;pwd=pwd;SERVER=SERVER;")
ts = ""
i = 3
Sql = data.Cells(2, 4)
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)
If rs.EOF Then
MsgBox "index value not available"
Else
Dim qt As QueryTable
data.Range("a3:bb60000").ClearContents ' empty sheet
Set qt = data.QueryTables.Add(rs, data.Range("a3"))
qt.AdjustColumnWidth = False ' qt need not adjust width
qt.BackgroundQuery = False ' dont run on background.
qt.PreserveFormatting = True
qt.RefreshStyle = xlOverwriteCells
qt.FieldNames = True
qt.Refresh
tmpstr = data.Name & "!" & qt.Name
qt.Delete ' rid of the querytable
' delete the name that is created is using query table
For Each n In wk.Names
If n.Name = tmpstr Then
n.Delete
Exit For
End If
Next n
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
datapullerr:
MsgBox (" Download Error; Operation aborted ")
'rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
So basically that clears the contents of the sheet and downloads directly
from the server based on the SQL statement that I place within the data.cells
field on the worksheet. I also want to eliminate that procedure as well and
incorporate the SQL query statement within the code. Also I tried eliminating
the Clearcontents function of the sheet but I'm getting errors when I do, if
anyone can shed some light on this it would be much appreciated.
Basically at the moment I wrote some code to download data from an SQL server
in which it queries the data when downloading. After it has finished
downloading on that page I would use VLOOKUPS to get the data from that page
but I now want to eliminate that extra procedure and have it download
directly onto the page in a single column, my code can be seen below:
Sub SPICEdownload_query()
On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("SHEETNAME")
Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
Dim ts As String
Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=DSN;UID=UID;pwd=pwd;SERVER=SERVER;")
ts = ""
i = 3
Sql = data.Cells(2, 4)
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)
If rs.EOF Then
MsgBox "index value not available"
Else
Dim qt As QueryTable
data.Range("a3:bb60000").ClearContents ' empty sheet
Set qt = data.QueryTables.Add(rs, data.Range("a3"))
qt.AdjustColumnWidth = False ' qt need not adjust width
qt.BackgroundQuery = False ' dont run on background.
qt.PreserveFormatting = True
qt.RefreshStyle = xlOverwriteCells
qt.FieldNames = True
qt.Refresh
tmpstr = data.Name & "!" & qt.Name
qt.Delete ' rid of the querytable
' delete the name that is created is using query table
For Each n In wk.Names
If n.Name = tmpstr Then
n.Delete
Exit For
End If
Next n
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
datapullerr:
MsgBox (" Download Error; Operation aborted ")
'rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub
So basically that clears the contents of the sheet and downloads directly
from the server based on the SQL statement that I place within the data.cells
field on the worksheet. I also want to eliminate that procedure as well and
incorporate the SQL query statement within the code. Also I tried eliminating
the Clearcontents function of the sheet but I'm getting errors when I do, if
anyone can shed some light on this it would be much appreciated.