Import Web Query Data into Excel

Hi,

Using either VBA code or the Excel functionality of Pivot Tables, how would I construct an SQL query to import data from a SQL server database into a pivot table, but filtering out certain items from one of the fields before it gets to the pivot table?

For example what's the syntax of the Select statement to extract records where say the field named "Actioned" = "N"

Usual TIA

Hello,

I run multiple Queries in MS access and then export the output individually to seperate excel work books for further analysis.

I need help in automating this process. Is it possible to have all this done by just clicking a botton.

I want the process to

1. run all the ms access queries
2. Import all the data in excel. Output of each query is imported in a seperate worksheet in the same workbook.
3. Save the workbook in a specified location. Since this is a periodic excercise i want the file names to be named after the date on which this process is run.

Is it possible to write a macro which can do all the above.

I have a very basic undertanding of macros.

Thanks in advance.

A.S.

I am currently using the "New Web Query" feature in Excel 2003 to access a web service which returns XML data. Excel correctly parses and displays this data into columns as I want it to.

My problem is regarding Excel not saving the datasource after I close and reopen the program. I have entered the "XML Map Properties" dialog and checked "Save data source definition in workbook" before I close the program.

When I reopen and try to refresh the data, I get the error:

Code:

Microsoft Office Excel encountered errors when importing the following files:

Source                                     Error         Import Status
http://mysite:9080/app/Service    Data source not found         Failed

My theory is that because the data is XML, Excel assumes it came from an XML file on my filesystem, even though the data source is clearly a URL that I created with the "New Web Query" functionality.

Any idea how to resolve this?? Thanks in advance.

i am trying to import data from many websites into excel.
the problem involves football teams and how many corners they took in each match. (don't ask!)

each webage has a predictable pattern for the address, so i have worked out that only a number changes in the address by 1 to get the next page or match.

eg

http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140

is the first match.
i am interested in gathering the two team names
SC Heerenveen
Excelsior

and the number of corners each ("Hoekschoppen")
4
5

i am trying to get these 4 bits of data into a row in excel, then underneath data from the next match:

http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140

the number in the middle of the address decreases by 1 each time, so the first few would be

http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140
http://www.vi.nl/Wedstrijden/Wedstri...peofpage=84140

is there a way i can automate this process?
(i have a list of all 306 addresses / matches in excel)

any help / hints appreciated

thanks in advance.

Hello,
I'm trying to import data from delimited text files without Excel saving the query or data connection. I used the Macro Recorder during the manual import, and unchecked the default "Save query definition" and selected "overwrite..." from the Import Data --Properties--External Data Range Properties dialog box:
Capture.jpg
VB:

Sub ImportDataWithUI() 
     
     ' ##############   BEGIN DATA IMPORT ##################
     ' Import overwrites cell range without inserting new columns
     'Get the file name with a UI from
     'Walkenbach's Sub GetImportFileName() page 410
     
    Dim Filt As String 
    Dim FilterIndex As Integer 
    Dim Title As String 
    Dim FileName As Variant 
     
     '   Set up list of file filters
    Filt = "Text Files (*.txt),*.txt,(*.csv),*.csv,(*.dat),*.dat," & _ 
    "All Files (*.*),*.*" 
     
     '   Display *.* by default
    FilterIndex = 1 
     
     '   Set the dialog box caption
    Title = "Select a File to Import" 
     
     '   Get the file name
    FileName = Application.GetOpenFilename _ 
    (FileFilter:=Filt, _ 
    FilterIndex:=FilterIndex, _ 
    Title:=Title) 
     
     '   Exit if dialog box canceled
    If FileName = False Then 
        MsgBox "No file was selected." 
        Exit Sub 
    End If 
     
     '   Display full path and name of the file
     '    MsgBox "You selected " & FileName
     '
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;" & FileName _ 
        , Destination:=Range("$A$1")) 
        .Name = FileName 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlOverwriteCells 
        .SavePassword = False 
        .SaveData = False 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 437 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = True 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = False 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
         
         
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Unfortunately, the code recorded fails to remove the query definition. So I searched around an found two subs that should do the trick, but don't:

VB:

 ' From:   http://www.mrexcel.com/forum/showthread.php?t=381140
 
Dim Sh As Worksheet, xNazwa As Object 
Dim xConect As Object 
 
For Each xConect In ActiveWorkbook.Connections 
    If UCase(xConect.Name) Like "*" Then xConect.delete 
Next xConect 
 
For Each Sh In ActiveWorkbook.Worksheets 
    For Each xNazwa In Sh.Names 
        xNazwa.delete 
    Next xNazwa 
Next Sh 
 
 'From http://www.ozgrid.com/forum/showthread.php?t=63309
Dim ws As Worksheet 
Dim qt As QueryTable 
For Each ws In ThisWorkbook.Worksheets 
    For Each qt In ws.QueryTables 
        qt.delete 
    Next qt 
Next ws 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



You see, I need to make a table from this data (bogus example file is attached) and if there is a data connection or a query, Excel throws a runtime error 1004 (table cannot intersect query). Once I get the data in, I run the following code to get the size of the data range (where the header row is, where the data rows start and end, and the last column). This code works until I turn it into a table, where things have ground to a halt. Here's the last part of the code:
VB:

Dim DataCellStart As Range 
Dim DataHeaderRow As Integer 
Dim DataRowStart As Integer 'for starting data calculations
Dim DataRowEnd As Long 
Dim DataColEnd As Integer 'this is the last column of imported data
 
Application.GoTo Reference:="R1C1" 
Selection.End(xlDown).Select 
Selection.End(xlDown).Select 
DataHeaderRow = Selection.Row 
Selection.End(xlDown).Select 
DataRowEnd = Selection.Row 
Selection.End(xlToRight).Select 
DataColEnd = Selection.Column 
 
DataRowStart = DataHeaderRow + 1 
 
Range("E1") = "Data Header Row" 
Range("E2") = DataHeaderRow 
Range("E3") = "Last Data Row" 
Range("E4") = DataRowEnd 
Range("E5") = "Last Data Column" 
Range("E6") = DataColEnd 
 
 'Automatic sizing of column widths, selecting like ctrl+shift+*
ActiveCell.CurrentRegion.Select 
 
 'Auto-size columns
Selection.Columns.AutoFit 
 
 'create a table from the imported data and give it a name
 
ActiveSheet.ListObjects.Add(xlSrcRange, , xlYes).Name = "ImportedData" 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



There was only one post in ozgrid that I found with the same problem but there was no solution:
http://www.ozgrid.com/forum/showthre...ht=import+text

I'll be grateful for any suggestions. I hope the explanation and breakdown is clear.

I am using the following:

Sub GetQueryDef()
'This sub will get data from an Existing QueryDef in my
'database and place the data on sheet1.

Dim db As Database
Dim Qd As QueryDef
Dim rs As Recordset
Dim Ws As Object
Dim i As Integer
Dim Path As String

'Set the Path to the database. This line is useful because
'if your database is in another location, you just need to change
'it here and the Path Variable will be used throughout the code.
'

Path = "G:\London\Home Depot\Common\Jose Guido\CreditAppTrack 11-21-06 Original DO NOT USE.mdb"

'Set Ws
Set Ws = Sheets("SameDayDecision")

'This set of code will activate Sheet1 and clear any existing data.
'After clearing the data, it will select cell A8.
Ws.Activate
Range("A8").Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A8").Select

'Set the Database and QueryDef. This QueryDef exists in the
'database.
Set db = Workspaces(0).OpenDatabase(Path, True, False)
Set Qd = db.QueryDefs("qry_Ops_SameDayDeci")
'Create a new Recordset from the Query based on the stored
'QueryDef.
Set rs = Qd.OpenRecordset()

'This loop will collect the field names and place them in the first
'row starting at "A8."
For i = 0 To rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = rs.Fields(i).Name
Next

'This line simply sets the font to bold for the headers.
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, rs.Fields.Count)).Font.Bold _
= True

'The next line will get the data from the recordset and copy it
'into the Worksheet (Sheet1).

Ws.Range("A9").CopyFromRecordset rs

'This next code set will just select the data region and auto-fit
'the columns
Sheets("SameDayDecision").Select
Range("A8").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A8").Select

Qd.Close
rs.Close
db.Close

End Sub

However, I keep getting runtime error

"Error 3061, too few parameters. Expected 2."

The debugger is stopping at

Set rs = Qd.OpenRecordset()

Am I missing something when defining

db.QueryDefs("qry_Ops_SameDayDeci")

?

Please help!

Thanks.

Jose