VBA import file macro

[Pinaceous]

Hi All,

I'm working with a great macro that allows the user to select a specific excel file in importing it into their already open excel workbook.

Dim wsDest As Worksheet
Set wsDest = ActiveWorkbook.Sheets(11)
Application.ScreenUpdating = False
On Error Resume Next
With Workbooks.Open(Application.GetOpenFilename("Excel Files, *.xls*"))
.Sheets(10).Range("A1:Q178").Copy wsDest.Range("A1:Q178")
.Close False
End With
On Error GoTo 0
Application.ScreenUpdating = True
Set wsDest = Nothing

Have at it and run the code.


It pops up your typical file to open box.


Now my question is; if the user clicks the 'Cancel' button I'd like a for a message box to pop up and say:

MsgBox "Not IMPORTING File!!"

and then to

Exit Sub

Can someone help me piece this together??

Thank you!
Pinaceous

 

[Fluff]

Re: VBA import file macro, really cool

Try

    Dim sFilename As Variant
    Dim wsDest As Worksheet

    ChDrive "C:"
    ChDir "c:\temp files\folder y\folder x"
    
    sFilename = Application.GetOpenFilename("Excel Files, *.xls*")
    If sFilename = False Then
        MsgBox "Not IMPORTING File!!"
        Exit Sub
    End If
    
    Set wsDest = ActiveWorkbook.Sheets(11)
    
Application.ScreenUpdating = False
    On Error Resume Next
    With Workbooks.Open(sFilename)
        .Sheets(10).Range("A1:Q178").Copy wsDest.Range("A1:Q178")
        .Close False
    End With
    On Error GoTo 0
Application.ScreenUpdating = True

Set wsDest = Nothing

 

[Pinaceous]

Re: VBA import file macro, really cool

Yes, that's what I want. I was reading about the ChDrive application.

Thank you for bringing it together for me!!!

 

[Fluff]

Re: VBA import file macro, really cool

Glad to help & thanks for the feedback