CODEWITHSUNDEEP
vbaexcelreference
Bildircin13
Bildircin13

Reputation: 113

Reference to a workbook selected with filedialog in vba

I am trying to create a sort of user interface in Excel and need to find a simple solution to copy a worksheet from a selected workbook to a specific workbook. So far I've written the code below but I don't know how to reference the selected workbook in code. Users are going to need to copy the worksheet ( it's called Storyboard and name never changes ) from many different workbooks ( with different names ) to the workbook with the VBA. Right now another workbook can be selected, but I cannot refer to that workbook in the codes.

Also, there are some macros on the workbooks that users going to select, how can I disable them after opening?

Here is the code so far;

Sub Storyboard_Ekle()
    Dim DosyaSec As Office.FileDialog

    Set DosyaSec = Application.FileDialog(msoFileDialogFilePicker)

   With DosyaSec

      .AllowMultiSelect = False
      .Title = "Lütfen yeni eklenecek Storyboard dosyasini seçiniz."
      .Filters.Clear
      .Filters.Add "Excel Macro-Enabled Workbook", "*.xlsm"
      .Filters.Add "Excel Workbook", "*.xlsx"
      .Filters.Add "All Files", "*.*"

      If .Show = True Then
        YeniSB = .SelectedItems(1)
      End If

Dim YeniStoryBoard As Workbook
Dim AnaDosya As Workbook
Dim YeniStoryBoard_Sheet As Worksheet
Dim AnaDosya_Sheet As Worksheet

Application.ScreenUpdating = False

Set AnaDosya = ThisWorkbook
Application.EnableEvents = False
Set YeniStoryBoard = Workbooks.Open(YeniSB)

YeniStoryBoard.Worksheets("Storyboard").Copy After:=ThisWorkbook.Worksheets("Kunye")
YeniStoryBoard.Close

Set YeniStoryBoard_isim = Sheets("Storyboard")
YeniStoryBoard_isim.Name = "StoryboardXXYYZZ"
Application.EnableEvents = True

   End With
End Sub

Thank you so much. :)

Upvotes: 0

Views: 658

Answers (1)

kevin
kevin

Reputation: 183

I think you should using this solution to disable them after opening

Application.EnableEvents = False            'disable Events
Set YeniStoryBoard = Workbooks.Open(YeniSB) 'open workbook 
Application.EnableEvents = True             'enable Events

To ensure disable event of workbook, You can use additional statements

YeniStoryBoard.Application.EnableEvents = False
'Do something
YeniStoryBoard.Application.EnableEvents = True
YeniStoryBoard.Close

Upvotes: 1

Related Questions