Announcement

Collapse
No announcement yet.

Open Multiple PRN Files

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Open Multiple PRN Files



    Hi, i have this code below that opens a prn file. What i want it to do now is to open the next prn file within the folder. I want it to continue until it opens all of them in the folder......So i'm a bit stuck now. I'ts driving me mad. Can someone put me out of my misery

    Thanks




    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 03/05/2007 by bousficj
    '
    
    '
        Dir ("D:\FTP data\Order Assessment\*.prn")
        Workbooks.OpenText Filename:="D:\FTP data\Order Assessment\*.prn", _
            Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
            Array(Array(0, 1), Array(8, 1), Array(12, 1), Array(31, 1), Array(44, 1), Array(59, 1), _
            Array(72, 1), Array(84, 1), Array(93, 1), Array(103, 1), Array(114, 1), Array(123, 1))
            
    End Sub

  • #2
    Re: Open All Files

    See if this link helps you out.

    z

    Comment


    • #3
      Re: Open All Files

      Thanks mate... I've taken a look at that link. The problem is that as much as i can get basic macros to work i'm not that clued up on them. I've done a lot of reading but don't understand most of it.

      I was hoping someone would tell me what code to add on to make it work..

      Still, thanks anyway

      Comment


      • #4
        Re: Open All Files

        Hi,

        This is what I use to loop through all files in a certain folder:

        Code:
        Sub Macro1()
        
        
        ' This function is called DO ... LOOP
        
            Dim AllFolderFiles()
            Dim wb As Workbook
            Dim TheFile As String
            Dim MyPath As String
            MyPath = "C:\My Document\Here are your files"    'location of files
            ChDir MyPath
            TheFile = Dir("*.prn")
            Do While TheFile <> ""   'will start LOOP until all files in folder MyPath have been looped through
                Set wb = Workbooks.Open(MyPath & "\" & TheFile)  'opens the file
                    
                ' enter your actions here
        
                 wb.Close
                TheFile = Dir
            Loop
        
        ' End of LOOP
        End Sub
        Good luck
        ---------------------------------------------------
        Check out this new band:
        www.statechaos.nl


        ---------------------------------------------------

        Comment


        • #5
          Re: Open All Files

          Mate, your a hero. i've entered my code like you said and it opens every file.
          Here's my code below now. Why will it not apply my actions to each sheat it opens. It'll only do it to the first file.
          Thanks a lot

          Code:
          Dim AllFolderFiles()
              Dim wb As Workbook
              Dim TheFile As String
              Dim MyPath As String
              MyPath = "D:\FTP data\Order Assessment"
              ChDir MyPath
              TheFile = Dir("*.prn")
              Do While TheFile <> ""
                  Set wb = Workbooks.Open(MyPath & "\" & TheFile)
                  Workbooks.OpenText Filename:="D:\FTP data\Order Assessment\*.prn", _
                  Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
                  Array(Array(0, 1), Array(8, 1), Array(12, 1), Array(31, 1), Array(44, 1), Array(59, 1), _
                  Array(72, 1), Array(84, 1), Array(93, 1), Array(103, 1), Array(114, 1), Array(123, 1))
                  TheFile = Dir
              Loop
          
          End Sub

          Comment


          • #6
            Re: Open All Files

            Just guesses:

            - I suppose all files/sheets are unprotected?
            - Or ... It looks like you try to open files twice, namely the first in this part of the code:
            Code:
            Set wb = Workbooks.Open(MyPath & "\" & TheFile)
            And then again in this part:

            Code:
            Workbooks.OpenText Filename:="D:\FTP data\Order Assessment\*.prn"
            Maybe you could try to merge your part in the open command of the loop. Something like this:

            Code:
            Set wb = Workbooks.Open(MyPath & "\" & TheFile), _ 
                Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _ 
                 Array(Array(0, 1), Array(8, 1), Array(12, 1), Array(31, 1), Array(44, 1), Array(59, 1), _ 
                Array(72, 1), Array(84, 1), Array(93, 1), Array(103, 1), Array(114, 1), Array(123, 1))
            Hope it helps.
            ---------------------------------------------------
            Check out this new band:
            www.statechaos.nl


            ---------------------------------------------------

            Comment


            • #7
              Re: Open Multiple PRN Files

              Cheers, i changed it but it only carries out my actions on the first file it opens

              I'm sure we're almost there

              Code:
              Dim AllFolderFiles()
              Dim wb As Workbook
              Dim TheFile As String
              Dim MyPath As String
              MyPath = "D:\FTP data\Order Assessment"
              ChDir MyPath
              TheFile = Dir("*.prn")
              Do While TheFile <> ""
                  Set wb = Workbooks.Open(MyPath & "\" & TheFile)
                  Workbooks.OpenText Filename:="D:\FTP data\Order Assessment\*.prn", _
                  Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
                   Array(Array(0, 1), Array(8, 1), Array(12, 1), Array(31, 1), Array(44, 1), Array(59, 1), _
                  Array(72, 1), Array(84, 1), Array(93, 1), Array(103, 1), Array(114, 1), Array(123, 1))
                  TheFile = Dir
               Loop
               
              End Sub

              Comment


              • #8
                Re: Open Multiple PRN Files

                Is this the code you use now?

                Cheers, i changed it but it only carries out my actions on the first file it opens

                I'm sure we're almost there


                VBA:
                Code:
                Dim AllFolderFiles() 
                Dim wb As Workbook 
                Dim TheFile As String 
                Dim MyPath As String 
                MyPath = "D:\FTP data\Order Assessment" 
                ChDir MyPath 
                TheFile = Dir("*.prn") 
                Do While TheFile <> "" 
                    Set wb = Workbooks.Open(MyPath & "\" & TheFile) 
                    Workbooks.OpenText Filename:="D:\FTP data\Order Assessment\*.prn", _ 
                    Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _ 
                     Array(Array(0, 1), Array(8, 1), Array(12, 1), Array(31, 1), Array(44, 1), Array(59, 1), _ 
                    Array(72, 1), Array(84, 1), Array(93, 1), Array(103, 1), Array(114, 1), Array(123, 1)) 
                    TheFile = Dir 
                 Loop 
                 
                End Sub
                ---------------------------------------------------
                Check out this new band:
                www.statechaos.nl


                ---------------------------------------------------

                Comment


                • #9
                  Re: Open Multiple PRN Files

                  Yes mate........Sorry to be a pain.

                  Thanks

                  Comment


                  • #10


                    Re: Open Multiple PRN Files

                    Thanks.......Got it working a treat

                    : D : D : D

                    Comment

                    Working...
                    X