Query Table refresh Error

[amanpadia1]

Hi.

I am using vba to send a web query to a website which takes date as an input and gives the historical files for different dates.

I set up my query using the QueryTables.Add(Connection...
function and with every iteration the URl string changes for the next date.

My problem is, when the loop encounters a date for which file is not available the .Refresh BackgroundQuery:=False gives error. For the first error i am able to by pass it and jump to the next date, but it doesnt get value for the next date and crashes there at the same location .Refresh BackgroundQuery:=False.

I have tried everything, on every iteration i am deleting the previous query and also clearing the contents , etc. But it still doesnt work.

Can anyone please help.

[Norie]

Can you post the code?

[amanpadia1]

Given below is the code. This code is only for getting the data.I wanted to extract that data and work upon it, that code i would be writing later. For the moment this code itself fails on encountering a missing file.The main file should have dates in dd-mm-yyyy format in column "A" from A2 onwards. Cell " A1" is defined as Dates.

Sub GetData()

On Error GoTo Aman
Dim IExp As InternetExplorer
Dim iter As Range
Dim Nsefile As Workbook
Dim tmpfile As Workbook
Dim tmpstr As String, tmpname As String
Dim totalDates, i As Integer
Dim fso


Application.Worksheets("Main").Activate

' count for how many dates
Set iter = Range("Dates").Offset(1, 0)
totalDates = 0
While iter.Value <> ""
totalDates = totalDates + 1
Set iter = iter.Offset(1, 0)
Wend

If totalDates > 0 Then
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FolderExists(ThisWorkbook.Path & "\NSE Dated files") Then
fso.CreateFolder (ThisWorkbook.Path & "\NSE Dated files")
End If
End If

Set iter = Range("Dates")
Application.DisplayAlerts = False
Workbooks.Add
Set tmpfile = Excel.ActiveWorkbook
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete

For i = 1 To totalDates

'
tmpstr = "Text;" & "http://www.nseindia.com/content/historical/DERIVATIVES/" _
& Right(Format(iter.Offset(i, 0).Value, "dd-mm-yyyy"), 4) & "/" _
& UCase(Left(Format(iter.Offset(i, 0).Value, "mmm-dd-yyyy"), 3)) _
& "/fo" & UCase(Format(iter.Offset(i, 0).Value, "ddmmmyyyy")) & "bhav.csv"
tmpname = "fo" & UCase(Format(iter.Offset(i, 0).Value, "ddmmmyyyy")) & "bhav"
'
tmpfile.Activate
Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "Sheet1"
Worksheets("Sheet1").Activate
Cells().ClearContents
Range("A1").Activate
Application.DisplayAlerts = False

With ActiveSheet.QueryTables.Add(Connection:=tmpstr _
, Destination:=Range("A1"))
.Name = tmpname
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=True

End With

On Error Go To Aman


Aman:

tmpfile.Activate
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Delete

Next i

End Sub

Edited 20-Jul-06 by geekgirlau. Reason: insert vba tags

[amanpadia1]

Hi.

I have posted the code .

Can some one help me figure this out.

Thanks