Hello all,

I've come across a problem that has stumped me and now I'm ready to
pull my hair out.

I query a website for a file each day (or various dates). I have no
problem getting the CSV file off the website and importing it into
Excel. One cell is set to a date - this date is then converted to a
string and put into the URL.

If I try to pull the file and the file does not happen to exist, Excel
gets the error 1004 - file does not exist. That is expected.

But, if I change the date to a file that I know does exist, Excel will
not attempt to refetch the page. Instead it gives me the 1004 error,
despite the fact that it is refering to the new URL that does
exist.

Here is the code:

Sub import()
'
' import Macro
'

'
d = Range("tomorrow").Value

Dim dateString As String
Dim qt As QueryTable
Dim conn As String
Dim dest As Range

dateString = ""

' year is always 4 digits, so we don't need to modify it
dateString = dateString & Year(d)

Dim tempInt As Integer

tempInt = Month(d)

' add a 0 in front of the month number if it's less than 10
If tempInt < 10 Then
dateString = dateString & "0" & tempInt
Else
dateString = dateString & tempInt
End If

' add a 0 in front of the day number if it's less than 10
tempInt = Day(d)

If tempInt < 10 Then
dateString = dateString & "0" & tempInt
Else
dateString = dateString & tempInt
End If

' now dateString is of the form yyyymmdd

On Error GoTo err_file

conn = "TEXT;http://www.nepool.com/histRpts/da-lmp/lmp_da_" &
dateString & ".csv"
Set dest = Worksheets("DA Pull").Range("A34")

If Worksheets("DA Pull").QueryTables.Count 0 Then
Set qt = Worksheets("DA Pull").QueryTables(1)
qt.Connection = conn
qt.Destination = dest
Else
Set qt = Worksheets("DA
Pull").QueryTables.Add(Connection:=conn, Destination:=dest)
End If


With qt
.Name = "PUB_GenPlan"
On Error GoTo err_file
.RobustConnect = xlNever
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.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, 1, 1, 1, 1, 1, _
1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=True
.SaveData = True
End With

Exit Sub

err_file:
qt.Delete
Set qt = Nothing
Exit Sub

End Sub

This is run on XP and Excel 2003

Thanks!!

-Zeqe

I am not sure if it will work, but try adding the statement "On Error
Resume Next" after the "End With". It worked for me. Good Luck.
-pb

What if you change this line
..Refresh BackgroundQuery:=True
to =False

NickHK

wrote in message
ups.com...
Hello all,

I've come across a problem that has stumped me and now I'm ready to
pull my hair out.

I query a website for a file each day (or various dates). I have no
problem getting the CSV file off the website and importing it into
Excel. One cell is set to a date - this date is then converted to a
string and put into the URL.

If I try to pull the file and the file does not happen to exist, Excel
gets the error 1004 - file does not exist. That is expected.

But, if I change the date to a file that I know does exist, Excel will
not attempt to refetch the page. Instead it gives me the 1004 error,
despite the fact that it is refering to the new URL that does
exist.

Here is the code:

Sub import()
'
' import Macro
'

'
d = Range("tomorrow").Value

Dim dateString As String
Dim qt As QueryTable
Dim conn As String
Dim dest As Range

dateString = ""

' year is always 4 digits, so we don't need to modify it
dateString = dateString & Year(d)

Dim tempInt As Integer

tempInt = Month(d)

' add a 0 in front of the month number if it's less than 10
If tempInt < 10 Then
dateString = dateString & "0" & tempInt
Else
dateString = dateString & tempInt
End If

' add a 0 in front of the day number if it's less than 10
tempInt = Day(d)

If tempInt < 10 Then
dateString = dateString & "0" & tempInt
Else
dateString = dateString & tempInt
End If

' now dateString is of the form yyyymmdd

On Error GoTo err_file

conn = "TEXT;http://www.nepool.com/histRpts/da-lmp/lmp_da_" &
dateString & ".csv"
Set dest = Worksheets("DA Pull").Range("A34")

If Worksheets("DA Pull").QueryTables.Count 0 Then
Set qt = Worksheets("DA Pull").QueryTables(1)
qt.Connection = conn
qt.Destination = dest
Else
Set qt = Worksheets("DA
Pull").QueryTables.Add(Connection:=conn, Destination:=dest)
End If


With qt
.Name = "PUB_GenPlan"
On Error GoTo err_file
.RobustConnect = xlNever
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.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, 1, 1, 1, 1, 1, _
1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=True
.SaveData = True
End With

Exit Sub

err_file:
qt.Delete
Set qt = Nothing
Exit Sub

End Sub

This is run on XP and Excel 2003

Thanks!!

-Zeqe