Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Open Multiple PRN Files

  1. #1
    Join Date
    19th January 2007
    Posts
    13

    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




    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    Re: Open All Files

    See if this link helps you out.

    z

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th January 2007
    Posts
    13

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th April 2007
    Location
    Sittard - The Netherlands
    Posts
    14

    Re: Open All Files

    Hi,

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

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th January 2007
    Posts
    13

    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

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th April 2007
    Location
    Sittard - The Netherlands
    Posts
    14

    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:
    VB:
    Set wb = Workbooks.Open(MyPath & "\" & TheFile) 
    
    
    And then again in this part:

    VB:
    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:

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th January 2007
    Posts
    13

    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

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th April 2007
    Location
    Sittard - The Netherlands
    Posts
    14

    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:
    VB:
    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


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

  9. #9
    Join Date
    19th January 2007
    Posts
    13

    Re: Open Multiple PRN Files

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

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th January 2007
    Posts
    13

    Re: Open Multiple PRN Files

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

    : D : D : D

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Open Multiple Files Via GetOpenFileName
    By richadj4 in forum EXCEL HELP
    Replies: 3
    Last Post: March 27th, 2008, 08:54
  2. Open Multiple Files
    By nicosd in forum EXCEL HELP
    Replies: 5
    Last Post: October 3rd, 2007, 16:29
  3. Allow User To Open Multiple Files At Once
    By brunilda in forum EXCEL HELP
    Replies: 1
    Last Post: September 29th, 2007, 04:43
  4. Replies: 8
    Last Post: November 15th, 2005, 03:39
  5. Tries to open multiple files on launch
    By tonyg in forum EXCEL HELP
    Replies: 1
    Last Post: April 13th, 2005, 15:57

Bookmarks

Posting Permissions

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