Copying sheet from external file

[adrianadik]

Hi all,

I'm trying to create VBA code which opens file from external drive, than copies selected sheet and pastes it into active workbook. That's what I've done so far:

Sub CopyWorksheet(control As IRibbonControl)

Application.ScreenUpdating = False
Application.DisplayAlerts = False


Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet


Set wbSource = Workbooks.Open("FILE PATH\file.xlsx", , True)
Set wsSource = wbSource.Worksheets(1)
Set wbDest = ThisWorkbook
Set wsDest = Sheets(1)


wbSource.ws.Source.Copy wbDest.wsDest


wbSource.Close


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub

Code opens workbook but (I suppose) it treats already opened workbook (source) as 'this workbook'.

Is there any way to copy sheet from opened file?

Regards from Poland

 

[wist]

Hi,

this should work

Sub CopyWorksheet()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wbSource As Workbook, wbDest As Workbook
Dim wsSource As Worksheet, wsDest As Worksheet

Set wbDest = ThisWorkbook
Set wsDest = wbDest.Sheets(1)
Set wbSource = Workbooks.Open("[COLOR=#574123]FILE PATH\[/COLOR]\file.xlsx", , True)
Set wsSource = wbSource.Sheets(1)

wsSource.Copy wsDest

wbSource.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

regards from Slovakia

 

[adrianadik]

Dakujem wist! Works perfect

I got one more issue. Macro is controlled by custom ribbon.

When user will open new excel window (and will not edit anything) code will open external file in this window - this will crash macro.

Is there any way to force opening file in new (seperate?) window to avoid this bug?

Regards,

 

[wist]

sorry, dont have experience with custom ribbons