Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts

    macro text box for opening 4 digit file number

    I need a macro that will open a text box asking for a number between 0-5000 and when the number is entered it will open a ".pfa" file with that 4 digit number. The file will start with 4 digits and have an "_" then say something else. I just need it to recognize the number. Also if some one puts 1 or 2 digits it will autofill the first of the four with zeros. Can this be done also if you can add 'what each code line means in detail I would really appreciate it I cam currently trying to learn to write macros so I can use the side comments. Thanks

  2. #2
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Does anyone have a macro for this?

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Herman,

    I'll give this a shot but I can't get to it until later today or tomorrow.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Herman Escobedo (2014-10-29)

  5. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Homework?

  6. #5
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    When ever you could get this would be perfect!! No its not homework

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Herman,

    Here's some code that works with the proviso that you want to open the file in excel (you didn't specify). If you want to open it in another program the code will need to be changed. Note that there are a couple of lines that you will need to change with your info they are marked by comments starting with <--
    Code:
    Option Explicit
    
    Sub OpenNumberedFile()
    
       Dim iFileNo     As Integer
       Dim zFileName   As String
       Dim zPath       As String
       Dim zFileExt    As String
       Dim zPadFile    As String   'Necessary for error msg!
       Dim wkb         As Workbook
       
       zPath = "G:\BEKDocs\"   '<-- Your path to the files here
       zFileExt = ".csv"       '<-- Your file extension here
       
       On Error GoTo ErrorTrap  'Set Error Procedure
       
       iFileNo = InputBox("Enter your file number 1-5000", "File Number:")
       
       If iFileNo = 0 Then 'User clicked the X, Cancel button or entered text!.
       Else                'Continue Processing
         zFileName = Format(iFileNo)              'Convert file number to text
         '*** Pad with zeros to length of 4 ***
         zPadFile = IIf(Len(zFileName) < 4, zFileName & _
                         String(4 - Len(zFileName), "0"), zFileName)
         '*** Check that file exists! ***
         zFileName = Dir(zPath & zPadFile & "_*" & zFileExt)
         If zFileName = "" Then  '*** File does not exist ***
           MsgBox "File No: " & zPadFile & " does not exist!", _
                   vbCritical + vbOKOnly, _
                   "Error: Non Existant File"
    
         Else        '*** OK Open File ***
           Workbooks.Open Filename:=zFileName
         End If   'zFileName = ""
         
       End If
          
       Exit Sub   'Protect ErrorTrap!
       
    ErrorTrap:
    
      Select Case Err
         Case 13   'User canceled with X
             Resume Next
         Case 1004
             MsgBox "File: " & zFileName & " does not exist!", _
                           vbCritical + vbOKOnly, _
                           "Error: Non Existant File"
         Case Else 'Untrapped errors
             MsgBox "An untrapped error (error: " & Format(Err) & vbCrLf & _
                    "Error Message: " & Err.Description & vbCrLf & _
                    "Please contact the system programmer!", _
                    vbOKOnly + vbCritical, "Unexpected Error Encountered:"
         Exit Sub
      End Select
    
    End Sub   'OpenNumberedFile()
    Test File: Herman.xlsm

    P.S. The code could be a bit more efficient but I included some things for clarity and to help you learn as you requested.

    HTH
    Last edited by RetiredGeek; 2014-10-29 at 08:44.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Herman Escobedo (2014-10-29)

  9. #7
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Thank you RetiredGeek,
    It is a .pfa file. When I run the macro and put the number it adds zeros to the back- Ex:3200 instead of 0032, also the files has- the 4 digit number_"A word"_"Another word"
    Example:"0032_House_Attach" without the quotes. Is it possible for excell to open this .pfa file? Also .pfa is a profloor file if that helps. Thank you

  10. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Herman

    A .pfa file is a printer font ascii file. Did you mean a .pdf file?????

    zeddy

  11. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Herman,

    Just change this:
    Code:
         zPadFile = IIf(Len(zFileName) < 4, zFileName & _
                         String(4 - Len(zFileName), "0"), zFileName)
    To this:
    Code:
         zPadFile = IIf(Len(zFileName) < 4,  _
                         String(4 - Len(zFileName), "0") & zFileName, zFileName)
    Can you tell us more about "profloor" as I have no idea what this program is or does.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Herman Escobedo (2014-10-29)

  13. #10
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Hey Zeddy
    Its a pfa file. We build floorplans with pfa files.
    Thank you

  14. #11
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Ill change the code and get back to you.
    Thank you RetiredGeek!!

  15. #12
    Lounger
    Join Date
    Jul 2014
    Posts
    33
    Thanks
    7
    Thanked 3 Times in 3 Posts
    That's interesting this thread popped up. I spend a lot of time saving spreadsheets that are emailed to me and I've been working on an email script to auto save the attachments....

    I assume this logic could be applied to a 3 digit number RG? Incidentally it's for the same and similar spreadsheets to what you helped me trim the code with

  16. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Herman,

    Try this code:

    Code:
    Option Explicit
    
    Sub OpenNumberedFile()
    
       Dim iFileNo     As Integer
       Dim zFileName   As String
       Dim zPath       As String
       Dim zFileExt    As String
       Dim zPadFile    As String   'Necessary for error msg!
       Dim wkb         As Workbook
       
       zPath = "G:\BEKDocs\"   '<-- Your path to the files here
       zFileExt = ".csv"       '<-- Your file extension here
       
       On Error GoTo ErrorTrap  'Set Error Procedure
       
       iFileNo = InputBox("Enter your file number 1-5000", "File Number:")
       
       If iFileNo = 0 Then 'User clicked the X, Cancel button or entered text!.
       Else                'Continue Processing
         zFileName = Format(iFileNo)              'Convert file number to text
         '*** Pad with zeros to length of 4 ***
         zPadFile = IIf(Len(zFileName) < 4, _
                         String(4 - Len(zFileName), "0") & zFileName, zFileName)
         '*** Check that file exists! ***
         zFileName = Dir(zPath & zPadFile & "_*" & zFileExt)
         If zFileName = "" Then  '*** File does not exist ***
           MsgBox "File No: " & zPadFile & " does not exist!", _
                   vbCritical + vbOKOnly, _
                   "Error: Non Existant File"
    
         Else        '*** OK Open File ***
            '*** Change the program d:\path\filename.exe below to your ProFloor program ***
            Shell "C:\Program Files (x86)\Notepad++\Notepad++.exe " & _
                    zPath & zFileName, vbMinimizedFocus
         End If   'zFileName = ""
         
       End If
          
       Exit Sub   'Protect ErrorTrap!
       
    ErrorTrap:
    
      Select Case Err
         Case 13   'User canceled with X
             Resume Next
         Case 1004
             MsgBox "File: " & zFileName & " does not exist!", _
                           vbCritical + vbOKOnly, _
                           "Error: Non Existant File"
         Case Else 'Untrapped errors
             MsgBox "An untrapped error (error: " & Format(Err) & vbCrLf & _
                    "Error Message: " & Err.Description & vbCrLf & _
                    "Please contact the system programmer!", _
                    vbOKOnly + vbCritical, "Unexpected Error Encountered:"
         Exit Sub
      End Select
    
    End Sub   'OpenNumberedFile()
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Herman Escobedo (2014-10-29)

  18. #14
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    it keeps saying the file does not exist. I tried using a excel file with the .xlsx extension it did not work

  19. #15
    New Lounger
    Join Date
    Oct 2014
    Posts
    24
    Thanks
    19
    Thanked 0 Times in 0 Posts
    Profloor is a program used to Draw floorplans and insert categories such as garden center, grocery, different departments.

Page 1 of 3 123 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •