Community discussion forum

Search and open workbooks. Can you help?

• • Reply

  2 years ago

  by PaddyHeron

  Patrick Heron

  , United Kingdom

  Joined 2 years ago

  I am trying to teach myself VBA from a library book, and in truth I'm not that bright.

  I have created a userform on my excel tools menu which I was hoping to use to gain access to workbooks holding information previously created. I have looked through the book but cannot find any help with searching, and opening workbooks. I want to read all the workbooks in a folder and, if a date held in a variable on worksheet1 is today's date or earlier, printout worksheets held in the workbooks. Can anyone help?

  Thanks

  Paddy

  Report abuse
• • Reply

  2 years ago

  by AndrewV

  AndrewV

  , United Kingdom

  Joined 4 years ago

  Not going to give you the whole answer but, here is some help:

  If u run this macro it will ask you for a folder:

  Then it will ask you for a extension pattern - (*.xls)

  This will then display all workbooks that you need in your worksheet......

  Sub FileList()
  Dim mydir As String
  Dim myPattern As String
  Dim myFile As String
  Dim myRow As Integer
  myRow = 1
  mydir = InputBox("Enter Directory")
  myPattern = InputBox("Enter Pattern")
  myFile = Dir(mydir & myPattern)
  Do Until myFile = ""
  Cells(myRow, 1).Value = myFile
  myRow = myRow + 1
  myFile = Dir()
  Loop
  End Sub
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse