Opening an excel file & copying sheets using VBA

Question

I need to copy a couple of excel sheets ("Y", "X") from one file to the same sheet in another excel file (call it Z - the same file that I'm using the VBA on). my limitation is that the name and path of the first excel file (with the X,Y) are changing, therefore I'm trying to write something more generic using the "as String" and the Application.GetOpenFilename() command but I'm receiving an error.

Tried to separate into 2 different subs

Sub BrowseForFile()
    Dim sFileName As String
    sFileName = Application.GetOpenFilename(, , "open the file: " )

    If sFileName = "false" Then Exit Sub
    MsgBox sFileName
    Workbooks.Open (sFileName)
    Workbooks(sFileName).Sheets("X").Activate
    Stop

Runtime Error 9
file doesn't find (1004 I think)

at first glance force the type of file in `GetOpenFilename` to be ".xls*" and also Dim sFileName as Variant? — Scott Holtzman, Jul 01 '19 at 14:11
So what you can do is convert the `sFileName` to string. `.GetOpenFilename()` returns type `Variant`, however converting to string you can then manipulate file path and file name. — Dean, Jul 01 '19 at 14:15

Pᴇʜ · Answer 1 · 2019-07-01T14:19:27.860

0

If the user presses the Cancel button then the GetOpenFilename function returns a boolean False not a string "false" so you need to test for If sFileName = False Then and declare it as Variant.

If you open a workbook always reference it to a variable so you can use that to access the workbook easily

Dim OpenedWb As Workbook
Set OpenedWb = Workbooks.Open(sFileName)

Then you can use the variable Wb to reference the workbook you opened

OpenedWb.Worksheets("X").Activate

Note that using .Select and .Activate is a bad practice and should be avoided (How to avoid using Select in Excel VBA). Instead use another reference to reference your workbook.

Dim ws As Worksheet
Set ws = OpenedWb.Worksheets("X")

And access a range like ws.Range("A1").Value for example.

Sub BrowseForFile()
    Dim sFileName As Variant 'needs to be variant because cancel returns a boolean False
    sFileName = Application.GetOpenFilename(, , "open the file: " )

    If sFileName = False Then Exit Sub

    MsgBox sFileName

    Dim OpenedWb As Workbook
    Set OpenedWb = Workbooks.Open(sFileName)

    Dim ws As Worksheet
    Set ws = OpenedWb.Worksheets("X")

    MsgBox ws.Range("A1").Value 'output the value of `A1` in worksheet `X` of workbook `sFileName`
End Sub

edited Jul 01 '19 at 14:19

answered Jul 01 '19 at 14:15

Pᴇʜ

56,719
10
49
73

I think OP uses `.GetOpenFilename` so a user can specify the location of the file, and not have it implicitly defined. – Dean Jul 01 '19 at 14:17
Actually using a `String` instead of a `Variant` does an implicit `CStr()` on the returned `Variant` from `GetOpenFilename()` but can cause unexpected behaviour when checking for `"false"`, because `CStr(false)` is "False" and not "false"; also different Excel languages might use the local language equivalent for `False`. – Nacorid Jul 01 '19 at 14:31
@Peh, yes, sorry I see you have expanded on your original answer :) – Dean Jul 01 '19 at 14:32
1

@Nacorid Yes you are correct but I would not want to rely on a implicit string conversion here. Better to use `Variant` and check the correct type, so it returns a `Variant/Boolean` for cancel and a `Variant/String` if a filename was chosen. – Pᴇʜ Jul 01 '19 at 14:35
@Pᴇʜ thanks. however i need to copy the values in X sheet (which is in a file which the VBA isn't operating) to a different excel file (the ones the VBA is working in). while trying to apply the ws.Range("A1:DU300").Select i couldnt because the sheet isn't really open. the values in Sheet X in the SFileName should be copied to the Temp1 sheet. Thanks! – Eliad Harell Jul 07 '19 at 08:25
recieving the 1004 error "select method of range class failed" – Eliad Harell Jul 07 '19 at 08:35
@EliadHarell Don't `.Select` as I told in my answer. Select is a bad practice you don't need to select to copy something see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Just copy directly without selcting `ws.Range("A1:DU300").Copy` – Pᴇʜ Jul 08 '19 at 05:35

Opening an excel file & copying sheets using VBA

1 Answers1