+ Reply to Thread
Results 1 to 7 of 7

Need Help with Excel Macro - Open - Close File

  1. #1
    Registered User
    Join Date
    01-24-2012
    Location
    Montgomery AL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Need Help with Excel Macro - Open - Close File

    I have a macro that look to a cell for a file name, opens that file and copies some data to the worksheet I'm in and then closes the file. It works fine up to the point of closing the file.... and it just stays open. I was hoping someone could look at the macro below and see if they can spot my error. Just to explain the logic a bit, the macro is in a workbook named "familes" and the worksheet in named "Wk content". It opens a file named in cells (x, y) and the rest should be self explanatory.

    Any help would be greatly appreciated!

    Sub abridor()
    Dim x, y, c, r As Integer
    Dim trai As Double

    x = 3
    y = 27
    r = 4
    c = 5

    With Workbooks("families").Worksheets("Wk content")
    Do While Cells(x, y).Value <> Empty
    Workbooks.Open(Cells(x, y).Value).Worksheets("Sheet2").Activate
    trai = Range("c5").Value
    Workbooks("families").Worksheets("Wk content").Activate
    Cells(r, c).Value = trai
    Workbooks(Cells(x, y).Value).Close savechanges:=False
    r = r + 1
    x = x + 1
    Loop
    End With
    End Sub
    Last edited by Lpitt56; 01-26-2012 at 03:32 PM.

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Need Help with Excel Macro - Open - Close File

    Welcome to the forum.

    First of all, you should post your code within code tags (see forum rules).

    Also, whenever you use the With command, you need to ensure that all statements within the With & End With has a "." before it, for e.g .range("A1") or .cells(x,y).

    In your above code - you need to change it as follows -
    Please Login or Register  to view this content.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    01-24-2012
    Location
    Montgomery AL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need Help with Excel Macro - Open - Close File

    Thanks so much for the reply. I added to "." before cell but I am still getting the error and not closing the worksheet that was opened to copy from. I is stoping on the following line.

    "Workbooks(.Cells(x, y).Value).Close savechanges:=False"

    Thanks in advance for you help!

    [code]

    Sub abridor()
    Dim x, y, c, r As Integer
    Dim trai As Double

    x = 3
    y = 27
    r = 4
    c = 5

    With Workbooks("families").Worksheets("Wk content")
    Do While .Cells(x, y).Value <> Empty
    Workbooks.Open(.Cells(x, y).Value).Worksheets("Sheet2").Activate
    trai = activeworkbook.Range("c5").Value
    Workbooks("families").Worksheets("Wk content").Activate
    .Cells(r, c).Value = trai
    Workbooks(.Cells(x, y).Value).Close savechanges:=False
    r = r + 1
    x = x + 1
    Loop
    End With
    End Sub
    [code]

  4. #4
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Need Help with Excel Macro - Open - Close File

    1. your closing code tag needs to be [/code]
    2. This line
    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.
    otherwise the first three are declared as Variant.
    3. You did not mention an error before - what error?
    Good luck.

  5. #5
    Registered User
    Join Date
    01-24-2012
    Location
    Montgomery AL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need Help with Excel Macro - Open - Close File

    Thanks again for the information. Error may have been the wrong term. I meant the macro did not close the file as intended. If I ran debug it would stop on that line highlighted in yellow.

  6. #6
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Need Help with Excel Macro - Open - Close File

    Aha - it is the file path. Please see if this works better
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-24-2012
    Location
    Montgomery AL
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need Help with Excel Macro - Open - Close File

    Just want to let you know the reccomendations worked! Thanks so much for you help and patience!

+ 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