Open an excel file

Open an excel file

• oldguy1166

  Hi

  There is always more than one way to solve a problem ,,, just one small point I would add to Jon's, don't forget to revert to normal error handling (on error goto 0 ( )) once you are past this section of code.

  Peter Mo.

  
  

• Othello

  Oops!

  That piece of code should now look like this:

  for i=1 to iMax
  on error resume next
  set wb = workbooks.open(sPath & "\" & sFile)
  on error goto 0
  if not wb is nothing then exit for
  application.wait now + timevalue("00:00:10")
  next

  
  
  

• Jeremy Balliston

  Yes, FSO is nice, but if all you're doing is opening a file, it's not necessary. Try something like this:

  dim wb as workbook
  dim i as long
  dim iMax as long
  dim sFile as String
  dim sPath as String

  iMax = 100 ' maximum attempts

  sPath = "C:\Temp"
  sFile = "P01CAB.xls"

  for i=1 to iMax
  on error resume next
  set wb = workbooks.open(sPath & "\" & sFile)
  if not wb is nothing then exit for
  application.wait now + timevalue("00:00:10")
  next

  if wb is nothing then
  ' file was never found
  Exit Sub
  endif

  ' continue with procedure

  
  
  

• hpannu

  Peter Mo. advice to use File.Exists is spot on...

  You can use the Application.Wait method to pause a macro. There is a good example of this method in the help files. This will let you wait 10 seconds before checking if the file is available.

  
  
  

• cmatt

  This is what I was thinkin when I asked. Thanks much. Tom
  
  

• patricef

  Here's some additional info from the support engineer:

  About the question “how to Check if a file exists with vba”.

  Instead of using the VBA Dir & MkDir commands, I recommend using the FileSystemObject for file & folder manipulations. Here is a generalized function that will create a path an arbitrary number of levels deep. It returns True or False indicating success or not.

  //sample code

  ===

  Function CreateFilePath(ByVal FPath As String) As Boolean

  Dim FSO As FileSystemObject

  Dim ParsedPath As Variant

  Dim TempPath As String

  Dim i As Long

  On Error Resume Next

  Set FSO = New FileSystemObject

  ParsedPath = Split(FPath, "\")

  TempPath = ParsedPath(0)

  For i = 1 To UBound(ParsedPath)

  TempPath = TempPath & "\" & ParsedPath(i)

  FSO.CreateFolder (TempPath)

  Next i

  CreateFilePath = FSO.FolderExists(FPath)

  Set FSO = Nothing

  End Function

  ===

  This function requires that a reference to the Microsoft Scripting Runtime be set.

  -brenda (ISV Buddy Team)

  
  
  

• LJames

  Hi

  A "nicer" solution is to Reference the Microsoft Scripting Runtime and then

  Dim fso as FileSystemObject

  Set fso = New FileSystemObject

  If fso.FileExists(strFileSpec) Then ...

  Regards

  Peter Mo.

  
  

• BENL_PERFINEX

  This looks exactly like what I need...but when I do it I get an error. It doesn't like

  Dim fso as FileSystemObject

  It returns "User-defined type not defined."

  I noticed that you say I need to reference the microsoft scripting runtime. I'm guessing that's what I'm missing...if so, how do I do that

  Thanks much.

  Tom

  
  
  

• maria_tachi

  Hi Tom

  As you say, that's you're missing. When you're in VBA, click Tools, References, and then tick the box next to Microsoft Scripting Runtime.

  At this point you should also get access to the additional help information as well.

  Regards

  Peter Mo.

  
  

• loanwolf35

  This worked...as soon as I figured out what the Microsoft Scripting Runtime was! Thanks.
  
  
• Open an excel file