Page 1 of 4 123 ... LastLast
Results 1 to 15 of 54
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    file renaming with excel vba

    I am currently working on a macro which will solve this problem:

    I have a list of excel files about 1600 of them. I need to rename tghe files to a name of a cell located in each file. the cell
    location is the same for each file that needs to be the new file name. So I am building a macro which pulls in a list of
    all the files in a folder, prints them into column A on a seperate work book, then I need the macro to rename those files
    from a cell located inside each individual file like cell A1 for example. So i figure that I need another module in the
    macro to pull the data out of cell a1 and paste it into column b of the new workbook. this will be the name i need
    the file it came from to be.

    so basically a macro would need to open a folder, then go through each file copy a cell, paste it to a column
    then rename the file it came from to the data that was pull out of the cell.

    this is the code ive came up with, but im having no luck with the renaming part, thought you might have an idea. this code only pulls out a cell

    Sub Extractdata()
    Dim strPath AS String, shtDEST AS worksheet, lngloop AS LONG
    Dim wbsource AS workbook

    Application.screenUpdating = False
    strPath = "C:/folder"
    Set shtDest = ThisWorkbook.Sheets("sheet1")

    with Application.Filesearch
    .newsearch
    .FileType = msoFileTypeExcelworkbooks
    .LookIn = strPath
    .SearchSubfolder = False
    .Execute
    'Now search files, open the workbook and place its values
    'in this workbooks sheet1
    For lngLoop = 1 To .FoundFiles.Count
    Set wbSource = Workbooks.Open(.FoundFiles(lngLoop))
    shtDest.Cells(lngLoop, 1) = wbSource.Name
    shtDest.Cells(lngLoop, 2) = wbSource.Sheets("sheet1").Range("A1")
    shtDest.Cells(lngLoop, 3) = wbSource.Sheets("sheet1").Range("B1")
    Next lngLoop
    End with

    Application.ScreenUpdating = True

    End Sub

    if anyone know some other code to do this please share
    thanks Sean

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file renaming with excel vba

    Maybe like this:

    Sub RenameFiles()
    Dim strPath AS String, shtDEST AS worksheet, lngloop AS LONG
    Dim wbsource AS workbook
    Dim sOldName as String
    Dim sNewName as String
    Application.screenUpdating = False
    strPath = "C:/folder"
    Set shtDest = ThisWorkbook.Sheets("sheet1")

    with Application.Filesearch
    .newsearch
    .FileType = msoFileTypeExcelworkbooks
    .LookIn = strPath
    .SearchSubfolder = False
    .Execute
    'Now search files, open the workbook and place its values
    'in this workbooks sheet1
    For lngLoop = 1 To .FoundFiles.Count
    Set wbSource = Workbooks.Open(.FoundFiles(lngLoop))
    'Get Old Name
    sOldName = wbSource.Name
    'Get New Name
    sNewName = wbSource.Sheets("sheet1").Range("A1")
    'Close file, do not save changes due to a recalc
    wbSource.Close False
    'Reset Object variable
    Set wbSource=Nothing
    'Wait for windows to release the file
    DoEvents
    'Rename the file (same folder)
    Name strPath & "" & sOldName As strPath & "" & sNewName
    Next lngLoop
    End with
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file renaming with excel vba

    Welcome to Woody's Lounge!

    You don't really need to store the old and new names in a workbook. You can rename the workbooks in the loop itself:

    Sub RenameFiles()
    ' Path must include trailing backslash
    Const strPath = "C:Folder"
    Dim strFile As String
    Dim wbk As Workbook
    Dim strNewName As String
    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    Set wbk = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
    ' If the name in A1 contains .xls, omit & ".xls" from the next line
    strNewName = wbk.Worksheets("Sheet1").Range("A1") & ".xls"
    wbk.Close SaveChanges:=False
    Name strPath & strFile As strPath & strNewName
    strFile = Dir
    Loop
    Set wbk = Nothing
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file renaming with excel vba

    Great minds......
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file renaming with excel vba

    We even gave the macro the same name! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file renaming with excel vba

    Im getting an run time error '438' and when i debug it highlights
    .SearchSubfolder = False

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file renaming with excel vba

    Try

    .SearchSubfolders = False

  8. #8
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file renaming with excel vba

    thanks i try it

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file renaming with excel vba

    The macro is still showing the same error, maybe the excel spreadsheets that im trying to pull the information from are too complexed to do this
    Does anyone know a simple way to rename a large volume of files to a new name the same as what is contained in a cell in excel?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file renaming with excel vba

    Have you tried the code I posted (with the correct sheet name and cell substituted)?

  11. #11
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file renaming with excel vba

    I didn't specify the sheet name, so i changed.

    The files are located in a folder, inside this folder each file has its own folder, this is what i am trying to rename. I moved one of the files into the main folder to see if the macro would find .xls files, and It did but the code stops at

    "Name strPath & strFile As strPath & strNewName" and gives a run time error '53' file not found message

    do you know whats going on, for this macro to work how must my file layout be?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file renaming with excel vba

    The code expects that all files are in the folder specified by

    Const strPath = "C:Folder"

    not in subfolders of that folder. The line

    strNewName = wbk.Worksheets("Sheet1").Range("A1") & ".xls"

    specifies that the new name is in cell A1 on Sheet1; change this as needed. If the name in the cell includes .xls, omit the last part of the line:

    strNewName = wbk.Worksheets("Sheet1").Range("A1")

    I have tested the code on a folder with a limited number of files, and it worked correctly there; I don't have 1600 files to try it on.

  13. #13
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file renaming with excel vba

    Yes i have the macro working, I found that the problem is the data in the cell that will be the name is data from a macro. What would i use to ask the macro to pull the cells value out instead of =name ? Each file has its own folder and the folder name is what im trying to change another problem is some of the excel files are inside sub folder of it main folder. Will the loop process be able to open each folder look for .xls files, find the cell value and use that value to change the main folders name , my apologies for all the questions

    Sean

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: file renaming with excel vba

    1. The code wbk.Worksheets("Sheet1").Range("A1") ought to return the value of the cell.
    2. Both the subject of the thread and the first post indicate that you want to rename files, now it turns out that you want to rename folders instead of files. Why in heaven's name do you have 1600 files, each in its own folder? That seems extremely inefficient. Can't you put all the files in one folder?

  15. #15
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file renaming with excel vba

    ya it was bad loading and thats what i am trying to fix so i can have clean data for building my database.

Page 1 of 4 123 ... LastLast

Posting Permissions

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