+ Reply to Thread
Results 1 to 3 of 3

Macro is closing wrong workbook before going to the next workbook

Hybrid View

  1. #1
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Macro is closing wrong workbook before going to the next workbook

    I run most of my macros out of a file called "Personal.XLS" and I have this bit of code at the end of this particular macro which is closing personal.xls instead of the active file it is working on. It is set up on a loop and it should go to the next file in the directory but since it closes the file I run the macro from, the macro stops immediately.

    It appears that it believes "ThisWorkbook.Close" is referring to the file from which the macro is initiated.


     
         'Opens ALL files in a folder, manipulates then saves as Excel
    Dim fName As String
    
    'Path with the files to convert
        ChDir "C:\My Documents\Excel Files"
    'Create a list of the files in that folder
        fName = Dir("*")
    
    'Open files one at a time to process them
        Do While Len(fName) > 0
        'Open file
            Workbooks.OpenText Filename:=fName, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
                Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 3), _
                Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 3), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
                Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), _
                Array(33, 1), Array(34, 3), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1)), TrailingMinusNumbers:=True
            'Date format columns
                Range("H:H,O:O,AH:AH").NumberFormat = "m/d/yyyy"
            'your further code here
            
            'Save to same directory as Excel file with same name
                ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal, CreateBackup:=False
            'Get next filename
                fName = Dir
            'Close current file before moving on
                ThisWorkbook.Close
        Loop
        
    MsgBox "Complete"
    End Sub

    I need the macro to close the file its working on and move to the next file in the dir.

    Thanks!
    Last edited by curbster; 08-12-2009 at 06:08 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Macro is closing wrong workbook before going to the next workbook

    ActiveWorkbook.Close
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: Macro is closing wrong workbook before going to the next workbook

    You nailed it! thanks!

+ 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