opening external file to copy/move sheet to/from - vba

[lezawang]

Hi
I want to open an closed excel file (abc.xlsx) and then move/copy sheet from the current workbook to that file (abc.xlsx). With your help, I learnt how to do that. But I just found that, when I open the file using open() function, excel will consider that file as workbooks(2). If that is the case, then why I need this line of code below? why I need to set it to X? I can work on it using workbooks(2) object

[INDENT]' why I need to assign it to x
set x = application.workbooks.open("c:\abc.xlsx")[/INDENT]

Sub abc()
    Dim x As Workbook
    Dim y As String
    y = InputBox("enter file name")
    Set x = Application.Workbooks.Open(y)
    workbooks(1).sheets(1).copy before:= x.sheets(1)
    'i can use workbooks(2) instead of x
    Workbooks(2).Close
End Sub

 

[Fluff]

Try opening two workbooks & then run your code.

 

[lezawang]

Thank you. I opened 2 files beside the one I am working on. So I opened abc.xlsx first and then xyz.xlsx. When I said workbooks(2).close, excel closed abc.xlsx. So everything was normal? Still do not see why I need to do x.close instead of workbooks(2).close. Thanks

Sub abc()
    Dim x As Workbook
    Dim n As Workbook
    Dim y As String
    y = InputBox("enter file name")
    Set x = Application.Workbooks.Open(y)
    y = InputBox("enter file name")
    Set n = Application.Workbooks.Open(y)
    Workbooks(2).Close
End Sub

 

[Fluff]

Have 2 workbooks open & then run your original code, that way you will then have three workbooks open.

 

[lezawang]

Same result! my main file A.xlsx is open already, then opened B.xlsx (by clicking on it) then opened C.xlsx (by clicking). Now I have 3 files opened. I ran the code then B.xlsx got closed. Thank you.

 

[Fluff]

Which file was opened by the code?

 

[lezawang]

none. i opened them manually first the run the code as per your instructions.

 

[Fluff]

If you open 2 workbooks manually then run the code from post#1, which workbook closes?

 

[lezawang]

I am working on A, then opened B and C and then ran the code from post#1. The code asked me to open a file, I said open B again and after that excel closed this time C and kept A and B. This time different answer!

So the whole idea of assigning name to an open file (open workbook) is to help knowing the file (in this case called X) without worring about how many workbooks are opened and which workbooks(#) will be assigned to the opened one.

So When I say

set x = app.wkbs.open("file name")

I know the opened file is call X and i can keep working on that. Is that the main idea? thank you so much.

 

[Fluff]

That's the idea.