Re: TWO AMENDMENTS NEEDED IN MACRO - PLEASE HELP

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

On Aug 8, 11:19 am, Joel <J...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Sub OpenFile2()
Folder = "C:\Document\KK"
VRNumber = Range("A1").Value
If IsNumeric(VRNumber) And (Range("J51").Value <> "") And _
   Len(VRNumber) = 6 Then

   fName = Dir(Folder & "\*" & VRNumber & "*.xlsx")
   If fName = "" Then
      MsgBox "VIREMENT  *" & VRNumber & "*  DONT EXIST"
   Else
      Do While fName <> ""
         Workbooks.Open Filename:=Folder & "\" & fName

         fName = Dir()
      Loop
   End If
Else
   MsgBox "VIREMENT  *" & VRNumber & "*  DONT EXIST"
End If
End Sub



"K" wrote:
Hi all,  I have macro set on a button (see below)

***************************************************>
Sub OpenFile2()
Folder = "C:\Document\KK"
VRNumber = Range("A1").Value
fName = Dir(Folder & "\*" & VRNumber & "*.xlsx")
If fName = "" Or Range("J51").Value = "" Or Len(VRNumber) <> 6 Then
MsgBox "VIREMENT  *" & VRNumber & "*  DONT EXIST"
Else
Workbooks.Open Filename:=Folder & "\" & fName
End If
End Sub
<***************************************************

In Folder "C:\Document\KK" files names are like (see below)
Excel 553656.xlsm
Excel 556987 - Page 1.xlsm
Excel 556987 - Page 2.xlsm …… etc

I want two amendment in macro above. At the moment if I put 6 digits
number in cell "A1" which are in file name (mentioned above) and hit
the button then macro opens that file for me.  The first amendment I
want is to specify that only 6 digits numbers should be put in cell
"A1" by adding code line in above macro "Mid(fName,7,6)"  so if some
one put 6 digits like "Excel " macro should not open the file and it
should only open if the middle bit of file name which are 6 digit
numbers will be put in cell "A1".  The second amendment i want in
above macro as the last two files mentioned above got same 6 digits
number in their names, the only difference is the last bit which is
"Page 1" and "Page 2" so if i put 6 digits number which is "556987" in
cell "A1" and when I hit the button then macro should open all the
file which got that 6 digits number in their names.  I tried many
experiments but wasnt very successful.  Can any friend amend my macro
above and put these two amendments.  it will be much appricated.
Thanks in advance- Hide quoted text -

- Show quoted text -

Thanks lot Joel. That’s what I was looking for
.

Relevant Pages

  • Re: Deleting Code from a Worksheet is Locking Up
    ... Private VBCompToDelete As VBIDE.VBComponent ... execution terminates and control returns to Excel. ... I run this macro and it works. ...
    (microsoft.public.excel.programming)
  • Re: Deleting Code from a Worksheet is Locking Up
    ...     If VBCompToDelete Is Nothing Then ... execution terminates and control returns to Excel. ... call the macro, and it freezes inside of this macro (it never ... worksheet is running the Worksheet_Change event macro and is causing ...
    (microsoft.public.excel.programming)
  • Re: Help with number-handling macro
    ... macro which will call the right glyphs in the right order according to ... I found on the net your manual for Syriac numerals; ... corresponding to arabic digits; if the symbols for the tens ...
    (comp.text.tex)
  • TWO AMENDMENTS NEEDED IN MACRO - PLEASE HELP
    ... Excel 556987 - Page 1.xlsm ... I want two amendment in macro above. ... At the moment if I put 6 digits ...
    (microsoft.public.excel.programming)
  • Re: a little problem with .Range(.Cells(2,1),.Cells(2,49)).
    ... I have never used an Excel ... all the cells already have a border. ...   With oExcelSheet ... I used the Record Macro feature of Excel to provide the information ...
    (microsoft.public.scripting.vbscript)