Hi,
I was wondering if I could get some help with an automated query
macro. The powerhouse of the program can be seen below:
For i_iterator = 1 To UBound(Times)
current_time = Times(i_iterator)
For site_iterator = 1 To UBound(Arr)
current_site = Arr(site_iterator)
'Generate the updated web adress for precip data
current_web_precip = websites_precip(site_iterator, 1)
current_web_precip = current_web_precip & current_time
current_web_precip = "URL;" & current_web_precip
'Generate the updated web address for temp data
current_web_temp = websites_temp(site_iterator, 1)
current_web_temp = current_web_temp & current_time
current_web_temp = "URL;" & current_web_temp
'Update the storage locations within the current Excel
worksheet
current_storage_location_precip = storage_locations
(site_iterator, 1)
current_storage_location_temp = storage_locations
(site_iterator, 2)
'First acquire and paste the precip
data
With ActiveSheet.QueryTables.Add
(Connection:=current_web_precip, Destination:=Range
((current_storage_location_precip & "2")))
.Name = "00"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
All I am doing is updating the variable "current_web_precip" and
"current_storage_location_precip " but
"current_storage_location_precip " really just updates the column that
the data is stored in. I then perform the generic data extraction in
the With loop. The problem is sometimes the program gets really far
and sometimes it crashes on the first try. I intend to use this loop
structure alot, at least 924 times so I was wondering if the web
browser needs to be closed or something along those lines. Also a
sample url is as follows:
http://cdec.water.ca.gov/cgi-progs/selectQuery?station_id=BLC&dur_code=E&sensor_num=16&start_date=12/21/2008+08:00&end_date=12/22/2008+16:00
The error message that comes up specifically states:
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Run-time error '1004':
The file could not be accessed. Try on the following:
Make sure the specified folder exists.
Make sure the folder that contains the file is not read-only.
Make sure the file name does not contain any of the following
characters: < > ? [ ] : | or *
Make sure the file/path doesn't contain more than 218
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
I understand that the sample url given above contains the "?"
character which the error message seems to flag above in the
unapproved character list given. However the program has worked with
this url and other url's that I have tried despite the fact that the
question mark exists.
If anyone could lend me a hand in this debacle I would greatly
appreciate it.
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
