Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 13th, 2007, 07:17 PM
Registered User
Join Date: May 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Handling Non-Existing Web Files with Workbook.Open

I'm running some VBA to D/L some files (.csv files) from a web page/site. Works fine, except when the file doesn't exist. Thing is, I don't know if the file exists or not when I attempt to D/L it.

I'm using Workbook.open ("www.thewebpage.com/fileX.csv")...

which works fine until it hits the case where it can't find the file. Excel then pops a message saying effectively "Can't find the file dummy!" Of course, I want to automate this, as there's lot's of files to D/L. When this error occurs, how can I deal with it such that the VBA handles it and goes on to the next filename (that does exist) smoothly?

Old May 14th, 2007, 03:07 PM
Friend of Wrox
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts

Handle the opening of the file in a different routine.

Private Sub YourSub

  Dim oSource As Worksheet, sFile As String, sSource_Path As String, bHave_File As Boolean
  'All your previous code prior here
  Do While YourCriteria = True 'Whatever you are using to loop through each one.
    bHave_File = TryOpen(sSourcePath & "/" & sFile) 'Assumes that sSourcePath doesn't end with a '/' already.
    If bHave_File Then
      Set oSource = Workbooks(sFile).Activesheet
      'Your processing of open workbook
      Workbooks(sFile).Close 'Add any appropriate switches desired, I'd suggest explicitly telling it not to save
      sFile = NextFileName 'Whatever method you're determining next file name by
    End If
  'Any other out of loop processing

End Sub

Private Function TryOpen(sPassed As String) As Boolean

'Tries to open file, returning true if successful
  On Error Goto TryOpenFailed
    Workbook.Open Filename:=sPassed
  On Error Goto 0
  TryOpen = True

End Function

Hope this helps.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Workbook Open anup.bihani Excel VBA 2 November 30th, 2006 01:05 AM
UserForm1.show fails if another workbook is open gjelson VB How-To 3 June 27th, 2006 02:58 PM
Error when open workbook gastoncs Excel VBA 2 December 21st, 2005 12:43 PM
Save the Existing Workbook to Other Filename tks_muthu Excel VBA 1 November 29th, 2004 10:41 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.