Using SQL in Get External Data
 

I want to populate my spreadsheet with data from an external SQL Server
database. I've setup the ODBC data source as a DSN name and can access the
database's data with no problem.

My question is how do I run an SQL SELECT statement against that database.
Where can this statement reside?

Thx all

Using SQL in Get External Data
 

Hi

I'm not overly-knowledgeable about this, but I use Data / Get External Data
/ New Databse Query (on XL2K)

Hope this helps.

--
Andy.


"Hall" wrote in message
...
I want to populate my spreadsheet with data from an external SQL Server
database. I've setup the ODBC data source as a DSN name and can access
the
database's data with no problem.

My question is how do I run an SQL SELECT statement against that database.
Where can this statement reside?

Thx all

Using SQL in Get External Data
 

Hall

Here's some info to get you started

http://www.dicks-clicks.com/excel/ExternalData.htm


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Hall" wrote in message
...
I want to populate my spreadsheet with data from an external SQL Server
database. I've setup the ODBC data source as a DSN name and can access
the
database's data with no problem.

My question is how do I run an SQL SELECT statement against that database.
Where can this statement reside?

Thx all

Using SQL in Get External Data
 

Two methods:
Data... Get External Data... New Database Query should
bring up Microsoft Query (assuming it was installed as
part of the Office installation). You can build your
query in there, send the results back as a range (query
table) into your spreadsheet. Then updating is a breeze -
can even add parameters based on cell values. See help or
search MSDN for more.

Other method (for small amounts of data) is the
SQL.REQUEST function - see Help.

Ken


-----Original Message-----
I want to populate my spreadsheet with data from an
external SQL Server
database. I've setup the ODBC data source as a DSN name
and can access the
database's data with no problem.

My question is how do I run an SQL SELECT statement
against that database.
Where can this statement reside?

Thx all


.

Using SQL in Get External Data
 

Have a look at my replys on

http://www.excelforum.com/showthread...hreadid=187543

Which allows a user to downloading a file from the internet to you pc.

As the data URL change daily and I assume it follows a standard format
I beleive the code I posted some time ago in this forum will assist
you. You will need to add code to calculate the Daily URL.


---
Message posted from http://www.ExcelForum.com/

Using SQL in Get External Data
 

Download code pasted here as reply to the other link had some funn
charactors appearing in it






'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2003 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Const ERROR_SUCCESS As Long = 0
Private Const BINDF_GETNEWESTVERSION As Long = &H10
Private Const INTERNET_FLAG_RELOAD As Long = &H80000000



Sub AutoDownloadIntranetFile()
Dim sSourceUrl As String
Dim sLocalFile As String


sSourceUrl$ = = "http://www.?????"
sLocalFile$ = c:\Temp\Data.txt"

If DownloadFile(sSourceUrl, sLocalFile) = False Then
MsgBox "Error In Downloading File" _
& Chr(10) _
& "Cannot Continue", vbCritical
End
End If
End Sub


Public Function DownloadFile(sSourceUrl As String, _
sLocalFile As String) As Boolean

'Download the file. BINDF_GETNEWESTVERSION forces
'the API to download from the specified source.
'Passing 0& as dwReserved causes the locally-cached
'copy to be downloaded, if available. If the API
'returns ERROR_SUCCESS (0), DownloadFile returns True.

DownloadFile = URLDownloadToFile(0&, _
sSourceUrl, sLocalFile, _
BINDF_GETNEWESTVERSION, _
0&) = ERROR_SUCCESS
End Functio

--
Message posted from http://www.ExcelForum.com

Using SQL in Get External Data
 

thanks a lot. I shall try this code. But I forgot to add one more
information.
when I click the link"download file in dbf format" or in csv format it gives
a window("file download window) where
it asks whether to "open this file in the current location" or "save this
file to
disk" then If I click "save to disk" I am able to save the file to the
disk.
Later I open the dbf file in excel format and save it also in excel format
for further analysis

does this additional information above require any change in your code
because invoking the link does not give the the data
directly.



mudraker wrote in message
...
Download code pasted here as reply to the other link had some funny
charactors appearing in it






'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Copyright ©1996-2003 VBnet, Randy Birch, All Rights Reserved.
' Some pages may also contain other copyrights by the author.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' Distribution: You can freely use this code in your own
' applications, but you may not reproduce
' or publish this code on any web site,
' online service, or distribute as source
' on any media without express permission.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" _
(ByVal pCaller As Long, _
ByVal szURL As String, _
ByVal szFileName As String, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Const ERROR_SUCCESS As Long = 0
Private Const BINDF_GETNEWESTVERSION As Long = &H10
Private Const INTERNET_FLAG_RELOAD As Long = &H80000000



Sub AutoDownloadIntranetFile()
Dim sSourceUrl As String
Dim sLocalFile As String


sSourceUrl$ = = "http://www.?????"
sLocalFile$ = c:\Temp\Data.txt"

If DownloadFile(sSourceUrl, sLocalFile) = False Then
MsgBox "Error In Downloading File" _
& Chr(10) _
& "Cannot Continue", vbCritical
End
End If
End Sub


Public Function DownloadFile(sSourceUrl As String, _
sLocalFile As String) As Boolean

'Download the file. BINDF_GETNEWESTVERSION forces
'the API to download from the specified source.
'Passing 0& as dwReserved causes the locally-cached
'copy to be downloaded, if available. If the API
'returns ERROR_SUCCESS (0), DownloadFile returns True.

DownloadFile = URLDownloadToFile(0&, _
sSourceUrl, sLocalFile, _
BINDF_GETNEWESTVERSION, _
0&) = ERROR_SUCCESS
End Function


---
Message posted from http://www.ExcelForum.com/

Using SQL in Get External Data
 

I don't know if my code will be compatiable with what you require as
have only ever used it to download txt, csv, xls files that are sittin
in that format on the site.

The option to save as csv or dbf may make the code un usabale

If it does not work I will not be able to help you as I found the cod
originaly by doing a search using googl

--
Message posted from http://www.ExcelForum.com