+ Reply to Thread
Results 1 to 9 of 9

Open multiple "unknown" filenames within a macro (array setup)

  1. #1
    need_some_help
    Guest

    Open multiple "unknown" filenames within a macro (array setup)

    I'm writing a macro that opens a static folder and then copies specific data
    from all the files in that folder to manipulate. I can write the code to
    open a file by specifying the exact filename. I need to learn how to open a
    file (all the files in a particular folder) one by one without knowing:

    1)the filenames
    2)how many files are in the folder

    ahead of time. Please help

  2. #2
    Ken Wright
    Guest

    Re: Open multiple "unknown" filenames within a macro (array setup)

    Try here:-

    http://www.rondebruin.nl/tips.htm

    --
    Regards
    Ken....................... Microsoft MVP - Excel
    Sys Spec - Win XP Pro / XL 97/00/02/03

    ------------------------------*------------------------------*----------------
    It's easier to beg forgiveness than ask permission :-)
    ------------------------------*------------------------------*----------------

    "need_some_help" <need_some_help@discussions.microsoft.com> wrote in message
    news:27A1A15E-2C10-4E8D-8B15-1C076FC40A10@microsoft.com...
    > I'm writing a macro that opens a static folder and then copies specific
    > data
    > from all the files in that folder to manipulate. I can write the code to
    > open a file by specifying the exact filename. I need to learn how to open
    > a
    > file (all the files in a particular folder) one by one without knowing:
    >
    > 1)the filenames
    > 2)how many files are in the folder
    >
    > ahead of time. Please help




  3. #3
    need_some_help
    Guest

    Re: Open multiple "unknown" filenames within a macro (array setup)

    Cool This really makes sense. As I modify this to look at multiple workbooks
    (not just worksheets within the same workbook) it gets a little tricky. Any
    suggestions on how to set up the array to read in multiple filenames (i.e.
    some variation of this line For Each sh In Sheets(Array("Sheet1", "Sheet3")))
    Thanks much

    "Ken Wright" wrote:

    > Try here:-
    >
    > http://www.rondebruin.nl/tips.htm
    >
    > --
    > Regards
    > Ken....................... Microsoft MVP - Excel
    > Sys Spec - Win XP Pro / XL 97/00/02/03
    >
    > ------------------------------*------------------------------*----------------
    > It's easier to beg forgiveness than ask permission :-)
    > ------------------------------*------------------------------*----------------
    >
    > "need_some_help" <need_some_help@discussions.microsoft.com> wrote in message
    > news:27A1A15E-2C10-4E8D-8B15-1C076FC40A10@microsoft.com...
    > > I'm writing a macro that opens a static folder and then copies specific
    > > data
    > > from all the files in that folder to manipulate. I can write the code to
    > > open a file by specifying the exact filename. I need to learn how to open
    > > a
    > > file (all the files in a particular folder) one by one without knowing:
    > >
    > > 1)the filenames
    > > 2)how many files are in the folder
    > >
    > > ahead of time. Please help

    >
    >
    >


  4. #4
    Ron de Bruin
    Guest

    Re: Open multiple "unknown" filenames within a macro (array setup)

    See this for workbooks
    http://www.rondebruin.nl/copy3.htm


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "need_some_help" <needsomehelp@discussions.microsoft.com> wrote in message
    news:045B6C05-B299-44ED-AD57-5F1090CA3CF2@microsoft.com...
    > Cool This really makes sense. As I modify this to look at multiple workbooks
    > (not just worksheets within the same workbook) it gets a little tricky. Any
    > suggestions on how to set up the array to read in multiple filenames (i.e.
    > some variation of this line For Each sh In Sheets(Array("Sheet1", "Sheet3")))
    > Thanks much
    >
    > "Ken Wright" wrote:
    >
    >> Try here:-
    >>
    >> http://www.rondebruin.nl/tips.htm
    >>
    >> --
    >> Regards
    >> Ken....................... Microsoft MVP - Excel
    >> Sys Spec - Win XP Pro / XL 97/00/02/03
    >>
    >> ------------------------------*------------------------------*----------------
    >> It's easier to beg forgiveness than ask permission :-)
    >> ------------------------------*------------------------------*----------------
    >>
    >> "need_some_help" <need_some_help@discussions.microsoft.com> wrote in message
    >> news:27A1A15E-2C10-4E8D-8B15-1C076FC40A10@microsoft.com...
    >> > I'm writing a macro that opens a static folder and then copies specific
    >> > data
    >> > from all the files in that folder to manipulate. I can write the code to
    >> > open a file by specifying the exact filename. I need to learn how to open
    >> > a
    >> > file (all the files in a particular folder) one by one without knowing:
    >> >
    >> > 1)the filenames
    >> > 2)how many files are in the folder
    >> >
    >> > ahead of time. Please help

    >>
    >>
    >>




  5. #5
    Ken Wright
    Guest

    Re: Open multiple "unknown" filenames within a macro (array setup)

    Do you really need multiple filenames or are you just looking to pull all
    files within a folder as per your original post. If so then try a variant
    of this, though if you look through Ron's examples, this may actually have
    come from there anyway:-

    Sub CopyAllSheetsToOneFile()
    Dim i As Integer
    Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim ws As Worksheet

    Application.ScreenUpdating = False

    Set wbDest = ThisWorkbook
    ' or alternativwly Set wbDest = Workbooks.Add

    With Application.FileSearch
    .NewSearch
    .LookIn = "C:\TEST"
    .FileType = msoFileTypeExcelWorkbooks
    .Execute

    For i = 1 To .FoundFiles.Count
    Set wbSource = Workbooks.Open(.FoundFiles(i))
    For Each ws In wbSource.Worksheets
    ws.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)
    Next ws
    wbSource.Close
    Next i

    End With
    Application.ScreenUpdating = True

    End Sub

    --
    Regards
    Ken....................... Microsoft MVP - Excel
    Sys Spec - Win XP Pro / XL 97/00/02/03

    ------------------------------*------------------------------*----------------
    It's easier to beg forgiveness than ask permission :-)
    ------------------------------*------------------------------*----------------

    "need_some_help" <needsomehelp@discussions.microsoft.com> wrote in message
    news:045B6C05-B299-44ED-AD57-5F1090CA3CF2@microsoft.com...
    > Cool This really makes sense. As I modify this to look at multiple
    > workbooks
    > (not just worksheets within the same workbook) it gets a little tricky.
    > Any
    > suggestions on how to set up the array to read in multiple filenames (i.e.
    > some variation of this line For Each sh In Sheets(Array("Sheet1",
    > "Sheet3")))
    > Thanks much
    >
    > "Ken Wright" wrote:
    >
    >> Try here:-
    >>
    >> http://www.rondebruin.nl/tips.htm
    >>
    >> --
    >> Regards
    >> Ken....................... Microsoft MVP - Excel
    >> Sys Spec - Win XP Pro / XL 97/00/02/03
    >>
    >> ------------------------------*------------------------------*----------------
    >> It's easier to beg forgiveness than ask permission :-)
    >> ------------------------------*------------------------------*----------------
    >>
    >> "need_some_help" <need_some_help@discussions.microsoft.com> wrote in
    >> message
    >> news:27A1A15E-2C10-4E8D-8B15-1C076FC40A10@microsoft.com...
    >> > I'm writing a macro that opens a static folder and then copies specific
    >> > data
    >> > from all the files in that folder to manipulate. I can write the code
    >> > to
    >> > open a file by specifying the exact filename. I need to learn how to
    >> > open
    >> > a
    >> > file (all the files in a particular folder) one by one without knowing:
    >> >
    >> > 1)the filenames
    >> > 2)how many files are in the folder
    >> >
    >> > ahead of time. Please help

    >>
    >>
    >>




  6. #6
    Ken Wright
    Guest

    Re: Open multiple "unknown" filenames within a macro (array setup)

    Hey Ron - Good morning :-) (00:45 here in the UK)

    Regards
    Ken.......................

    "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
    news:uaNK5ykjFHA.3692@TK2MSFTNGP09.phx.gbl...
    > See this for workbooks
    > http://www.rondebruin.nl/copy3.htm
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "need_some_help" <needsomehelp@discussions.microsoft.com> wrote in message
    > news:045B6C05-B299-44ED-AD57-5F1090CA3CF2@microsoft.com...
    >> Cool This really makes sense. As I modify this to look at multiple
    >> workbooks
    >> (not just worksheets within the same workbook) it gets a little tricky.
    >> Any
    >> suggestions on how to set up the array to read in multiple filenames
    >> (i.e.
    >> some variation of this line For Each sh In Sheets(Array("Sheet1",
    >> "Sheet3")))
    >> Thanks much
    >>
    >> "Ken Wright" wrote:
    >>
    >>> Try here:-
    >>>
    >>> http://www.rondebruin.nl/tips.htm
    >>>
    >>> --
    >>> Regards
    >>> Ken....................... Microsoft MVP - Excel
    >>> Sys Spec - Win XP Pro / XL 97/00/02/03
    >>>
    >>> ------------------------------*------------------------------*----------------
    >>> It's easier to beg forgiveness than ask permission :-)
    >>> ------------------------------*------------------------------*----------------
    >>>
    >>> "need_some_help" <need_some_help@discussions.microsoft.com> wrote in
    >>> message
    >>> news:27A1A15E-2C10-4E8D-8B15-1C076FC40A10@microsoft.com...
    >>> > I'm writing a macro that opens a static folder and then copies
    >>> > specific
    >>> > data
    >>> > from all the files in that folder to manipulate. I can write the code
    >>> > to
    >>> > open a file by specifying the exact filename. I need to learn how to
    >>> > open
    >>> > a
    >>> > file (all the files in a particular folder) one by one without
    >>> > knowing:
    >>> >
    >>> > 1)the filenames
    >>> > 2)how many files are in the folder
    >>> >
    >>> > ahead of time. Please help
    >>>
    >>>
    >>>

    >
    >




  7. #7
    Ron de Bruin
    Guest

    Re: Open multiple "unknown" filenames within a macro (array setup)

    Hi Ken

    For me 1 hour later on that moment (much to late)

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message news:%23s12O4kjFHA.3300@TK2MSFTNGP15.phx.gbl...
    > Hey Ron - Good morning :-) (00:45 here in the UK)
    >
    > Regards
    > Ken.......................
    >
    > "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message news:uaNK5ykjFHA.3692@TK2MSFTNGP09.phx.gbl...
    >> See this for workbooks
    >> http://www.rondebruin.nl/copy3.htm
    >>
    >>
    >> --
    >> Regards Ron de Bruin
    >> http://www.rondebruin.nl
    >>
    >>
    >> "need_some_help" <needsomehelp@discussions.microsoft.com> wrote in message
    >> news:045B6C05-B299-44ED-AD57-5F1090CA3CF2@microsoft.com...
    >>> Cool This really makes sense. As I modify this to look at multiple workbooks
    >>> (not just worksheets within the same workbook) it gets a little tricky. Any
    >>> suggestions on how to set up the array to read in multiple filenames (i.e.
    >>> some variation of this line For Each sh In Sheets(Array("Sheet1", "Sheet3")))
    >>> Thanks much
    >>>
    >>> "Ken Wright" wrote:
    >>>
    >>>> Try here:-
    >>>>
    >>>> http://www.rondebruin.nl/tips.htm
    >>>>
    >>>> --
    >>>> Regards
    >>>> Ken....................... Microsoft MVP - Excel
    >>>> Sys Spec - Win XP Pro / XL 97/00/02/03
    >>>>
    >>>> ------------------------------*------------------------------*----------------
    >>>> It's easier to beg forgiveness than ask permission :-)
    >>>> ------------------------------*------------------------------*----------------
    >>>>
    >>>> "need_some_help" <need_some_help@discussions.microsoft.com> wrote in message
    >>>> news:27A1A15E-2C10-4E8D-8B15-1C076FC40A10@microsoft.com...
    >>>> > I'm writing a macro that opens a static folder and then copies specific
    >>>> > data
    >>>> > from all the files in that folder to manipulate. I can write the code to
    >>>> > open a file by specifying the exact filename. I need to learn how to open
    >>>> > a
    >>>> > file (all the files in a particular folder) one by one without knowing:
    >>>> >
    >>>> > 1)the filenames
    >>>> > 2)how many files are in the folder
    >>>> >
    >>>> > ahead of time. Please help
    >>>>
    >>>>
    >>>>

    >>
    >>

    >
    >




  8. #8
    need_some_help
    Guest

    Re: Open multiple "unknown" filenames within a macro (array setup)

    The workbook code Ron sent worked beautifully (Thanks guy). I've got a
    client who wants the macro to work "in whatever directory the macro is opened
    in". This will give the flexibility when it is not known in advanced where
    the list we're reading from is located (maybe on hard drive maybe on network
    etc) I think I'll use some sort of current directory function to tell the
    macro to perform the code "in the same directory where the macro is opened"

    Thanks for your intelligent input

    "Ken Wright" wrote:

    > Do you really need multiple filenames or are you just looking to pull all
    > files within a folder as per your original post. If so then try a variant
    > of this, though if you look through Ron's examples, this may actually have
    > come from there anyway:-
    >
    > Sub CopyAllSheetsToOneFile()
    > Dim i As Integer
    > Dim wbDest As Workbook
    > Dim wbSource As Workbook
    > Dim ws As Worksheet
    >
    > Application.ScreenUpdating = False
    >
    > Set wbDest = ThisWorkbook
    > ' or alternativwly Set wbDest = Workbooks.Add
    >
    > With Application.FileSearch
    > .NewSearch
    > .LookIn = "C:\TEST"
    > .FileType = msoFileTypeExcelWorkbooks
    > .Execute
    >
    > For i = 1 To .FoundFiles.Count
    > Set wbSource = Workbooks.Open(.FoundFiles(i))
    > For Each ws In wbSource.Worksheets
    > ws.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)
    > Next ws
    > wbSource.Close
    > Next i
    >
    > End With
    > Application.ScreenUpdating = True
    >
    > End Sub
    >
    > --
    > Regards
    > Ken....................... Microsoft MVP - Excel
    > Sys Spec - Win XP Pro / XL 97/00/02/03
    >
    > ------------------------------*------------------------------*----------------
    > It's easier to beg forgiveness than ask permission :-)
    > ------------------------------*------------------------------*----------------
    >
    > "need_some_help" <needsomehelp@discussions.microsoft.com> wrote in message
    > news:045B6C05-B299-44ED-AD57-5F1090CA3CF2@microsoft.com...
    > > Cool This really makes sense. As I modify this to look at multiple
    > > workbooks
    > > (not just worksheets within the same workbook) it gets a little tricky.
    > > Any
    > > suggestions on how to set up the array to read in multiple filenames (i.e.
    > > some variation of this line For Each sh In Sheets(Array("Sheet1",
    > > "Sheet3")))
    > > Thanks much
    > >
    > > "Ken Wright" wrote:
    > >
    > >> Try here:-
    > >>
    > >> http://www.rondebruin.nl/tips.htm
    > >>
    > >> --
    > >> Regards
    > >> Ken....................... Microsoft MVP - Excel
    > >> Sys Spec - Win XP Pro / XL 97/00/02/03
    > >>
    > >> ------------------------------*------------------------------*----------------
    > >> It's easier to beg forgiveness than ask permission :-)
    > >> ------------------------------*------------------------------*----------------
    > >>
    > >> "need_some_help" <need_some_help@discussions.microsoft.com> wrote in
    > >> message
    > >> news:27A1A15E-2C10-4E8D-8B15-1C076FC40A10@microsoft.com...
    > >> > I'm writing a macro that opens a static folder and then copies specific
    > >> > data
    > >> > from all the files in that folder to manipulate. I can write the code
    > >> > to
    > >> > open a file by specifying the exact filename. I need to learn how to
    > >> > open
    > >> > a
    > >> > file (all the files in a particular folder) one by one without knowing:
    > >> >
    > >> > 1)the filenames
    > >> > 2)how many files are in the folder
    > >> >
    > >> > ahead of time. Please help
    > >>
    > >>
    > >>

    >
    >
    >


  9. #9
    Ken Wright
    Guest

    Re: Open multiple "unknown" filenames within a macro (array setup)

    How about the following then:-

    Dump this all into a module, then set a reference to the Scripting runTime
    library as per the notes in the code and then run 'DoStuffToAllFiles'.

    ====================================================

    Function PickFolder(strStartDir As Variant) As String
    Dim SA As Object, F As Object
    Set SA = CreateObject("Shell.application")
    Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
    If (Not F Is Nothing) Then
    PickFolder = F.items.Item.path
    End If
    Set F = Nothing
    Set SA = Nothing
    End Function

    Sub DoStuffToAllFiles()
    'This uses the Microsoft Scripting Runtime library, so you need to set a
    'reference to that (Tools>References)

    Dim objFSO As Scripting.FileSystemObject
    Dim objFolder As Scripting.Folder
    Dim objSubfolder As Scripting.Folder
    Dim objFile As Scripting.File
    Dim Sh As Worksheet

    Application.ScreenUpdating = False

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    objfoldpath = PickFolder(strStartDir)
    Set objFolder = objFSO.GetFolder(objfoldpath)
    For Each objFile In objFolder.Files
    If objFile.Type = "Microsoft Excel Worksheet" Then
    Workbooks.Open Filename:=objFolder.path & "\" & objFile.Name

    With ActiveWorkbook
    .Names.Add Name:="NewYear", RefersToR1C1:="=Data!R2C6"
    For Each Sh In .Worksheets
    Cells.Replace what:="Total 2004", _
    Replacement:="=""Total ""&NewYear", _
    lookat:=xlPart, SearchOrder:=xlByRows,
    MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace what:="2004 YTD", _
    Replacement:="=NewYear&"" YTD""", _
    lookat:=xlPart, SearchOrder:=xlByRows,
    MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False

    Next Sh
    .Close SaveChanges:=True
    End With

    End If
    Next

    Application.ScreenUpdating = True
    End Sub

    ================================================

    This then allows you to pick a folder to work with via the normal browse
    type dialog box, stores the folder path as a variable and then carries on as
    normal.

    Marry up your code plus what we gave you earlier with this and it should
    hopefully do everything you want.

    --
    Regards
    Ken....................... Microsoft MVP - Excel
    Sys Spec - Win XP Pro / XL 97/00/02/03

    ------------------------------*------------------------------*----------------
    It's easier to beg forgiveness than ask permission :-)
    ------------------------------*------------------------------*----------------

    "need_some_help" <needsomehelp@discussions.microsoft.com> wrote in message
    news:D7B31712-EEBB-4636-8868-4C878F69CF3D@microsoft.com...
    > The workbook code Ron sent worked beautifully (Thanks guy). I've got a
    > client who wants the macro to work "in whatever directory the macro is
    > opened
    > in". This will give the flexibility when it is not known in advanced
    > where
    > the list we're reading from is located (maybe on hard drive maybe on
    > network
    > etc) I think I'll use some sort of current directory function to tell the
    > macro to perform the code "in the same directory where the macro is
    > opened"
    >
    > Thanks for your intelligent input
    >
    > "Ken Wright" wrote:
    >
    >> Do you really need multiple filenames or are you just looking to pull all
    >> files within a folder as per your original post. If so then try a
    >> variant
    >> of this, though if you look through Ron's examples, this may actually
    >> have
    >> come from there anyway:-
    >>
    >> Sub CopyAllSheetsToOneFile()
    >> Dim i As Integer
    >> Dim wbDest As Workbook
    >> Dim wbSource As Workbook
    >> Dim ws As Worksheet
    >>
    >> Application.ScreenUpdating = False
    >>
    >> Set wbDest = ThisWorkbook
    >> ' or alternativwly Set wbDest = Workbooks.Add
    >>
    >> With Application.FileSearch
    >> .NewSearch
    >> .LookIn = "C:\TEST"
    >> .FileType = msoFileTypeExcelWorkbooks
    >> .Execute
    >>
    >> For i = 1 To .FoundFiles.Count
    >> Set wbSource = Workbooks.Open(.FoundFiles(i))
    >> For Each ws In wbSource.Worksheets
    >> ws.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)
    >> Next ws
    >> wbSource.Close
    >> Next i
    >>
    >> End With
    >> Application.ScreenUpdating = True
    >>
    >> End Sub
    >>
    >> --
    >> Regards
    >> Ken....................... Microsoft MVP - Excel
    >> Sys Spec - Win XP Pro / XL 97/00/02/03
    >>
    >> ------------------------------*------------------------------*----------------
    >> It's easier to beg forgiveness than ask permission :-)
    >> ------------------------------*------------------------------*----------------
    >>
    >> "need_some_help" <needsomehelp@discussions.microsoft.com> wrote in
    >> message
    >> news:045B6C05-B299-44ED-AD57-5F1090CA3CF2@microsoft.com...
    >> > Cool This really makes sense. As I modify this to look at multiple
    >> > workbooks
    >> > (not just worksheets within the same workbook) it gets a little tricky.
    >> > Any
    >> > suggestions on how to set up the array to read in multiple filenames
    >> > (i.e.
    >> > some variation of this line For Each sh In Sheets(Array("Sheet1",
    >> > "Sheet3")))
    >> > Thanks much
    >> >
    >> > "Ken Wright" wrote:
    >> >
    >> >> Try here:-
    >> >>
    >> >> http://www.rondebruin.nl/tips.htm
    >> >>
    >> >> --
    >> >> Regards
    >> >> Ken....................... Microsoft MVP - Excel
    >> >> Sys Spec - Win XP Pro / XL 97/00/02/03
    >> >>
    >> >> ------------------------------*------------------------------*----------------
    >> >> It's easier to beg forgiveness than ask permission :-)
    >> >> ------------------------------*------------------------------*----------------
    >> >>
    >> >> "need_some_help" <need_some_help@discussions.microsoft.com> wrote in
    >> >> message
    >> >> news:27A1A15E-2C10-4E8D-8B15-1C076FC40A10@microsoft.com...
    >> >> > I'm writing a macro that opens a static folder and then copies
    >> >> > specific
    >> >> > data
    >> >> > from all the files in that folder to manipulate. I can write the
    >> >> > code
    >> >> > to
    >> >> > open a file by specifying the exact filename. I need to learn how
    >> >> > to
    >> >> > open
    >> >> > a
    >> >> > file (all the files in a particular folder) one by one without
    >> >> > knowing:
    >> >> >
    >> >> > 1)the filenames
    >> >> > 2)how many files are in the folder
    >> >> >
    >> >> > ahead of time. Please help
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1