none
work through file names. RRS feed

  • Question

  • Good evening all.

    Yes. I know. It is late.... Mind is still working, so I want to get it while I remember.

    I have a series of workbooks, where the names will be common, except for a 5 digit value in the middle of the name.

    I'll be copying data from each workbook, and want to deal with the variable 5 digit number in the middle.

    Eg.

    BK 01450 Food Cost

    The "BK", and the "Food Cost" will be identical, but the five digit- 01450 will differ.

    Presently, my code--- used record macro, making modifications as I developed it--- is

    Windows("BK 01450 Food Cost.xls").Activate

    How do I code this?I'm kind of thinking---

    Windows("BK" & MyVar & " Food Cost.xls").Activate

    TYIA

    Saturday, December 10, 2016 7:08 AM

Answers

  • Hi,

    >>I'd like to read through the directory of file names, and store them, then as I operate on each file, copy the data from them (BK xxxxx Food Costs.xls), and paste into the destination book.

     

    We could use Dir FileSystemObject object to loop every files in one folder. For more information, please visit  Dir Function & Accessing Files with FileSystemObject

     

    E.g.

    Sub LoopFolder()
     
    Dim file As Variant
    file = Dir("D:\test\")
    While (file <> "")
    Debug.Print file
    file = Dir
    Wend
     
    Dim objFSO, objFolder, objFile As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("D:\test\")
    For Each objFile In objFolder.Files
    Debug.Print objFile.Name
    Next
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    End Sub

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Chenchen LiModerator Friday, December 16, 2016 4:45 AM
    • Marked as answer by SteveDB1 Friday, December 16, 2016 5:07 AM
    Thursday, December 15, 2016 6:19 AM
    Moderator

All replies

  • Use like below

    Workbooks("BK " & MyVar & " Food Cost.xls").Activate

    Hope you assigned the digit to MyVar earlier.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Saturday, December 10, 2016 7:16 AM
    Answerer
  • Hi,

    I see no issues in it. But again, what is your question? Are you getting any error? Can you please elaborate on Problem part for which you are looking for the solution.


    Vish Mishra

    Saturday, December 10, 2016 7:16 AM
  • Hi,

    I see no issues in it. But again, what is your question? Are you getting any error? Can you please elaborate on Problem part for which you are looking for the solution.


    Vish Mishra

    Hi Vish.

    I guess I forgot we're on a 24 hour world nowadays. (I'm on the US west coast, so it's late where I am). I forget guys like you are just waking up, or mid way through your day. (assuming your name gives your location in India)

    My question has to do with how to state the variable in the

    Windows("BK 01450 Food Cost.xls").Activate

    statement.

    I suppose I could set it up as a case/switch statement. I haven't got that far yet.

    Ultimately,

    I'd like to read through the directory of file names, and store them, then as I operate on each file, copy the data from them (BK xxxxx Food Costs.xls), and paste into the destination book.

    I've already set up the copy/paste for a single pair. Now I need to expand it to include 30 workbooks, of different xxxxx #'s.

    The idea of opening each book, manually moving from directory to directory is really tedious. To automate it would be way easier.

    Thank you for your timely response.

    I'm going to bed now.

    Best.

    Saturday, December 10, 2016 7:29 AM
  • Hi,

    >>I'd like to read through the directory of file names, and store them, then as I operate on each file, copy the data from them (BK xxxxx Food Costs.xls), and paste into the destination book.

     

    We could use Dir FileSystemObject object to loop every files in one folder. For more information, please visit  Dir Function & Accessing Files with FileSystemObject

     

    E.g.

    Sub LoopFolder()
     
    Dim file As Variant
    file = Dir("D:\test\")
    While (file <> "")
    Debug.Print file
    file = Dir
    Wend
     
    Dim objFSO, objFolder, objFile As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("D:\test\")
    For Each objFile In objFolder.Files
    Debug.Print objFile.Name
    Next
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    End Sub

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Chenchen LiModerator Friday, December 16, 2016 4:45 AM
    • Marked as answer by SteveDB1 Friday, December 16, 2016 5:07 AM
    Thursday, December 15, 2016 6:19 AM
    Moderator
  • This should do what you want.

    http://www.rondebruin.nl/win/addins/rdbmerge.htm

    Use 'Merge every worksheet with a name that contains'.


    MY BOOK

    Monday, January 16, 2017 5:16 PM