none
File Left Open in Excel 2010 but not in Excel 2013 but Why? RRS feed

  • Question

  •    

    Hi All,

    After running the four lines of code below, if I try to open the 'MyFile.XLSX' in the windows Explorer I get a message saying  'File in use by 'another user'. However if I try to reference the file in Excel via VBA it is clearly not accessible (and shouldn't be). So why after copying all worksheets to another workbook is it still ready only?

    Is there a way of ensuring the file is writeable once again via VBA (since the object can not be referenced any longer?)

    Thanks in advance of any enlightenment

    Steve 

    Dim pmWkBk as Workbook

    Set pmWkBk = GetObject(Workbooks("MyFile.XLSX"))

    pmWkBk.Sheets.Move After:=Workbooks("MyOtherFile.XLSX").Worksheets("Sheet1")

    Set pmWkBk = Nothing


    Steve Stretch

    Friday, March 20, 2015 11:46 AM

Answers

  • There are 2 ways i could handle this:

    GetObject and Workbooks.open

    The reason I didn't want to use Workbooks.Open in Excel 2013 is because it switches off the Application.ScreenUpdating = FALSE  

    as I don't have this problem in Excel 2010, I will switch methods based on the version number of Excel.

    So I don;t know why but I have a workaround.

    Thansk for your help

    Steve


    Steve Stretch

    • Marked as answer by Stretchy10 Friday, March 20, 2015 3:42 PM
    Friday, March 20, 2015 3:42 PM

All replies

  • I think you need to close the Workbook first!

    Something like this:

    Workbook.Close
    Set pmWkBk = Nothing
    Set obj = Nothing


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, March 20, 2015 2:37 PM
  • Thanks for your response, well, this is kinda the problem, you see once all the sheets have been copied over the original workbook no longer exists in the Excel object model so if you tried to do

    Workbooks("MyFile.XLSX").Close

    you would get a subscript out of range error...

    ...a rock and a hard place...it seems to be closed and doesn't exist in Excel but when you try to open it again Excel decides it does still exist.

    Cheers


    Steve Stretch


    • Edited by Stretchy10 Friday, March 20, 2015 2:46 PM
    Friday, March 20, 2015 2:46 PM
  • So, 'the original workbook no longer exists in the Excel object model'.  I don't think that's possible. 

    If you turn on the Macro Recorder and click through the steps, you'll get the exact code you need to get this working.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, March 20, 2015 3:04 PM
  • So, 'the original workbook no longer exists in the Excel object model'.  I don't think that's possible.

    It is 100% possible because once all the sheets of a workbook have been MOVED to anther workbook the original workbook doest;t exist. try it yourself...code below was recorded and the last line added.

    Cheers

    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
        Workbooks.Open Filename:="C:\Users\Win7\Desktop\Hello.xlsx"
        Sheets("Hello").Select
        Sheets("Hello").Move Before:=Workbooks("Goodbye.xlsx").Sheets(1)

        'then the bit you say is impossible - this is where you would get a subscript out of range error
        Workbooks("Hello").Close

    End Sub


    Steve Stretch

    Friday, March 20, 2015 3:19 PM
  • There are 2 ways i could handle this:

    GetObject and Workbooks.open

    The reason I didn't want to use Workbooks.Open in Excel 2013 is because it switches off the Application.ScreenUpdating = FALSE  

    as I don't have this problem in Excel 2010, I will switch methods based on the version number of Excel.

    So I don;t know why but I have a workaround.

    Thansk for your help

    Steve


    Steve Stretch

    • Marked as answer by Stretchy10 Friday, March 20, 2015 3:42 PM
    Friday, March 20, 2015 3:42 PM
  • Hummm, maybe the code handles that automatically.  I never knew that.  I know for a fact you can't delete all sheets; you must have at least 1 sheet in a Workbook.  Similarly, you can't hide all sheets; you must have at least 1 visible sheet in a Workbook.

    Glad you got it all straightened out!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, March 20, 2015 4:18 PM