+ Reply to Thread
Results 1 to 16 of 16

Macro Loop Create Excel Files

Hybrid View

  1. #1
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Smile Macro Loop Create Excel Files

    Hello,

    I have two columns of filepaths in excel. I would like to write a macro that will open the excel file listed in Column D (with a rowcounter) and save it as a new file with the name/path in Column E (same rowcounter). I would like to loop through the entire list of file paths to do this quickly.

    Here is what I have so far. Can't seem to troubleshoot this though.

        Dim rowcounter As Integer
        Dim filepath As String
        Dim template_file As String
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("IAS")
        Dim xlapp As Excel.Application
           
        rowcounter = 3
           
        Do While Not IsEmpty(Sheets("IAS").Range("B" & rowcounter).Value)
           template_file = ws.Cells(rowcounter, 4).Value
           filepath = ws.Cells(rowcounter, 5).Value
           xlapp.Workbooks.Open Filename:=template_file
           ActiveWorkbook.SaveAs Filename:=filepath _
           , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
           ReadOnlyRecommended:=False, CreateBackup:=False
        rowcounter = rowcounter + 1
        Loop
    Last edited by gsurge; 09-15-2011 at 04:29 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro Loop Create Excel Files

    Hello gsurge,

    If all your files are on the same disk, you could use the Name As statement instead of opening and saving the files.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Macro Loop Create Excel Files

    Can I use that even if the files have not been created? I simply have a series of templates that I need duplated various times and renamed

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro Loop Create Excel Files

    Hello gsurge,

    In that case, the answer would be no. The file must already exist. You can iterate through range easier by using a Range variable that represents the cells with data. Here is an example.

        Dim filepath As String
        Dim Rng As Range
        Dim template_file As String
        Dim ws As Worksheet
        Dim xlapp As Excel.Application
    
        Set ws = ThisWorkbook.Worksheets("IAS")
            
        Set Rng = ws.Range("D3").End(xlDown)
        
        For each Cell In Rng
           template_file = Cell.Value
           filepath = Cell.Offset(0, 1)..Value
           xlapp.Workbooks.Open Filename:=template_file
           ActiveWorkbook.SaveAs Filename:=filepath _
           , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
           ReadOnlyRecommended:=False, CreateBackup:=False
        Next Cell

  5. #5
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Macro Loop Create Excel Files

    duplicated*

    At this point i am creating the files for the first time with this macro (via save as)

  6. #6
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Macro Loop Create Excel Files

    Thanks Leith, but I receive the following error: "object variable or With block variable not set"

    The debud highlights the following line:

    xlapp.Workbooks.Open Filename:=template_file

    Any idea??

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro Loop Create Excel Files

    Hello gsurge,

    I see the problem. My mistake there is a typo in the For Next loop. Here is the corrected code.

    Also I added a check for the "\" character in the file path.
        For Each Cell In Rng
           template_file = Cell.Value
           filepath = Cell.Offset(0, 1).Value
           filepath = IIf(Right(filepath, 1) <> "\", filepath & "\", filepath)
           xlapp.Workbooks.Open Filename:=template_file
           ActiveWorkbook.SaveAs Filename:=filepath _
           , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
           ReadOnlyRecommended:=False, CreateBackup:=False
        Next Cell

  8. #8
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Macro Loop Create Excel Files

    I actually corrected the typo, and still getting the same error on

    xlapp.Workbooks.Open Filename:=template_file

  9. #9
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macro Loop Create Excel Files

    Get rid of the "xlapp." bit?

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro Loop Create Excel Files

    Hello gsurge,

    If you are running this macro outside of Excel then you need to set the variable xlapp to an instance of Excel. Here is an example.
        Set xlApp = CreateObject("Excel.Application")

    If you are running the macro from inside Excel then simply delete the xlapp Dim statement and prefix from your code. It would look this...
        Dim filepath As String
        Dim Rng As Range
        Dim template_file As String
        Dim ws As Worksheet
    
        Set ws = ThisWorkbook.Worksheets("IAS")
            
        Set Rng = ws.Range("D3").End(xlDown)
    
    
        For Each Cell In Rng
           template_file = Cell.Value
           filepath = Cell.Offset(0, 1).Value
           filepath = IIf(Right(filepath, 1) <> "\", filepath & "\", filepath)
           Workbooks.Open Filename:=template_file
           ActiveWorkbook.SaveAs Filename:=filepath _
           , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
           ReadOnlyRecommended:=False, CreateBackup:=False
        Next Cell

  11. #11
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Macro Loop Create Excel Files

    Workbooks.Open Filename:=template_file is still giving me problems.

    I did not add the xlapp but the macro cannot seem to recognize the file path referenced by template_file

    Do you have any recommendations on why this is? I don't think its an issue with my filepath in the cell itself, and it is 100% spelled correctly and located in the folder.

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro Loop Create Excel Files

    Hello gsurge,

    When the errors occurs, you can move your cursor over the variables in the statement and VBA will show you what they contain. Check the file path is what you have on the sheet and that the path is corrected formatted.

  13. #13
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Macro Loop Create Excel Files

    Thank you for all of the help Leith. I solved the previous issue, but now the macro will not continue in the loop. I added an ActiveWorkbook.Close command before "Next Cell", but the macro simply executes for the first cell in the range, but doesnt continue. Any thoughts?

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Macro Loop Create Excel Files

    Hello gsurge,

    I set the macro to look down from the starting until it found a blank cell. Not the way I usually do it but I thought perhaps the data was all unbroken. This method ill find the cell in the column that contains data. The macro will need to skip blanks if they are encounterd. Try this macro and let me know.

        Dim filepath As String
        Dim Rng As Range
        Dim RngEnd As Range
        Dim template_file As String
        Dim ws As Worksheet
    
        Set ws = ThisWorkbook.Worksheets("IAS")
            
        Set Rng = ws.Range("D3")
        Set RngEnd = ws.Cells(Rows.Count, Rng.Column).End(xlUp)
        If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = ws.Range(Rng, RngEnd)
    
        For Each Cell In Rng
          If Cell <> "" Then
             template_file = Cell.Value
             filepath = Cell.Offset(0, 1).Value
             filepath = IIf(Right(filepath, 1) <> "\", filepath & "\", filepath)
             Workbooks.Open Filename:=template_file
             ActiveWorkbook.SaveAs Filename:=filepath , FileFormat:=xlNormal, _ 
                                   Password:="", WriteResPassword:="", _
                                   ReadOnlyRecommended:=False, CreateBackup:=False
           End If
        Next Cell

  15. #15
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Macro Loop Create Excel Files

    it is not broken, a consistent list in Column D

  16. #16
    Registered User
    Join Date
    05-23-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Macro Loop Create Excel Files

    figured it out...the following code worked. Thank you Leith for all of your help. You are a life saver

      Dim filepath As String
        Dim Rng As Range
        Dim template_file As String
    
        Dim ws As Worksheet
    
        Set ws = ThisWorkbook.Worksheets("IAS")
      
        Dim i As Integer
    
        Dim intRowCount As Integer
    
        intRowCount = Range("D3").CurrentRegion.Rows.Count - 1
    
        For i = 4 To intRowCount
           template_file = Cells(i, 4).Value
           filepath = Cells(i, 5).Value
           Workbooks.Open Filename:=template_file
           ActiveWorkbook.SaveAs Filename:=filepath _
           , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
           ReadOnlyRecommended:=False, CreateBackup:=False
           ActiveWorkbook.Close      
        Next i
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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