MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

web querying bug

  Asked By: Lorraine    Date: Sep 03    Category: MS Office    Views: 1514

I am using the following section of code to retrieve preliminary tidal
data from a NOAA gage. The program works fine if I am retrieving about
9 days worth of data or less. Any more than that and I get the error
"Excel cannot complete this task with available resources..." during the
copying portion.

I have other programs that retrieve many more lines of data without a
problem, so it shouldn't really be an issue with the amount of data.

Any thoughts?

Sub GetProvisionalData()

Dim Today_Date As Long
Dim Today_str As String
Dim PrelimStart_Date As Long
Dim PrelimStart_str As String

Dim LewesPreliminary_URL As String


' Selection.End(xlDown).Select
PrelimStart_Date = ActiveCell.Value + 1

Today_Date = Date * 1

'replace existing query:

Today_str = Format(Today_Date, "yyyymmdd")
PrelimStart_str = Format(PrelimStart_Date, "yyyymmdd")

' Get first batch of verified data

LewesPreliminary_URL =
"URL;co-ops.nos.noaa.gov/cgi-bin/co-ops_qry_direct.cgi" _
& PrelimStart_str & "&edate=" & Today_str &
"&date=3&shift=0&level=-1&form=0&host=" _
& "&addr="

With ActiveSheet.QueryTables.Add(Connection:=LewesPreliminary_URL,
.Name = "LewesPreliminary"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
'.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


' Worksheets("Control Sheet").Select

End Sub



2 Answers Found

Answer #1    Answered By: Luann Schmidt     Answered On: Sep 03

Excel seems to make heavy weather of 'Copying web  data to sheet'. I
got 'Not enough memory' messages for more than about 14 days.
The one line in your macro that on changing allowed me to get the
full 1 month's 6-minute data  quickly and without error was:
.WebSingleBlockTextImport = False
which I changed to:
.WebSingleBlockTextImport = True

A simple 'Text to columns' operation on the data (or all of column A)
using spaces as the delimiter restored the data cleanly to pretty
much the same state as your original macro and took no significant
Tag something like this to the end of the macro:

Rows("1:39").Delete Shift:=xlUp
Columns("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited,TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
Array(4, 5), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1), Array(12, 1))

Answer #2    Answered By: Garritt Bakker     Answered On: Sep 03

Above solution will work....

Didn't find what you were looking for? Find more on web querying bug Or get search suggestion and latest updates.