VBA Question - downloading files from internet

[Who is John Galt]

I am using Excel 2010, Windows XP.

I am interested in downloading from the internet archived excel files and save them to my "C" drive.

The data can be found here (scroll down a little): http://www.pjm.com/markets-and-opera...-time/lmp.aspx

Each file represents a day's worth of price data. I have written the following VBA code that works well enough with ONE BIG GLITCH, the website stops my code, generates a message box saying:

"Some files can contain viruses or otherwise be harmful to your computer ... Would you like to open this file?"

and waits for a response!!!

As a result any automation is stopped dead in its tracks because my code requires me to sit there and press "OK" to the previous question.

My question is ... "What VBA Code can I write to have the macro answer this question enabling me to use a loop download and save a months worth of files while enjoying a cup of coffee?"

I assume the solution is something along the lines of when closing a workbook without saving it ----> ActiveWorkbook.Close savechanges:=False


The code I have written is as follows (it is set up to download & save the 31 files representing the 31 days of August 2010):

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --></style>
Sub GetDailyData()<o></o>
<o> </o>
Dim WebSiteAddress As String<o></o>
<o> </o>
Dim DayNumber As Variant<o></o>
<o> </o>
DayNumber = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)<o></o>
<o> </o>
For i = 1 To 31<o></o>
<o> </o>
WebSiteAddress = "http://www.pjm.com/pub/account/lmp/201008" & DayNumber(i) & ".csv"<o></o>
<o></o>
ActiveWorkbook.FollowHyperlink Address:=WebSiteAddress, NewWindow:=True<o></o>
<o></o>
Dim WBO As Workbook ' original workbook<o></o>

Set WBO = ActiveWorkbook<o></o>

fn = "C:\PJM\August" & " " & i & ", 2010" & ".xlsm
<o></o>
WBO.SaveAs Filename:= fn<o></o>
WBO.Close SaveChanges:=False<o></o>
<o></o>
Next i<o></o>
<o></o>
[FONT=&quot]End Sub[/FONT]


Thank you in advance for any assistance that may be provided.

Sincerely,

Who is John Galt

 

[John_w]

No idea, but try this (Excel can open the file URLs directly using File - Open):

Sub GetDailyData2()
    Dim theDate As Date
    
    For theDate = DateValue("01-Aug-2010") To DateValue("31-Aug-2010")
        Workbooks.Open Filename:="http://www.pjm.com/pub/account/lmp/" & Format(theDate, "yyyymmdd") & ".csv"
        ActiveWorkbook.SaveAs Filename:="C:\PJM\" & Format(theDate, "mmmm d, yyyy") & ".xls"
        ActiveWorkbook.Close savechanges:=False
        DoEvents
    Next

End Sub

 

[Who is John Galt]

John -

Wow!!

Your bit of code worked perfectly!!

I am just beginning the journey of learning VBA.

Your post introduced me to the "DoEvents" and the "Format(theDate,"mmm,d,yyy")" concepts.

I am going to look those up to get clear on how these concepts work.

Thank you for taking the time to post a reply!!

Some day soon, I hope to pay it forward on this forum with a post replying to a question I know the answer to.

Sincerely,

"Who is John Galt?"

 

[John_w]

"What is the purpose of the 'DoEvents' at the end of the loop?"

Better to respond here rather than by PM so that other people benefit.

The For Next loop is a continuous loop which runs for a long time and therefore hogs the CPU time allocated to Excel. DoEvents allows Excel to be fairly responsive to other actions by the user - things like scrolling a worksheet, interacting with the UI, interrupting the macro, etc. - while the subroutine is running. Mouse and keyboard events are managed by the operating system and DoEvents yields execution to the OS so that it can send them to Excel to perform the required action.