automate windows prompt responses

[dmurphy1991]

Ok so my below code basically opens another macro enabled workbook takes out a sheet (with a specific name) and pastes it into my active worksheet, Unfortunately when opening the file (to be copied from) i have to respond whether to enable macros or not. As this is within my code by opening another sheet i wasn't sure if there was a way to auto select 'enable'.

Alternatively is there a way to adjust y code so the file never has to be physically opened? by the user still specifies the file to be used?

Any help is really appreciated thanks!

Option Explicit
Sub Sample()
Dim wb1 As Workbook, wb2 As Workbook
Dim Ret1
Set wb1 = ActiveWorkbook
'~~> Get the first File
Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select the setup automator file")
If Ret1 = False Then Exit Sub
Set wb2 = Workbooks.Open(Ret1)
Application.DisplayAlerts = False
wb1.Sheets.Add.Name = "Setup Data Export"
wb2.Activate
Sheets("Setup Data").Select
Range("A14").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A14:CP14").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=9
Selection.Copy
wb1.Sheets("Setup Data Export").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
wb2.Close SaveChanges:=False
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:CP").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 73
ActiveWindow.ScrollColumn = 71
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 1
Range("A2").Select



End Sub

 

[Andrew Poulsom]

You can't automatically enable macros in VBA. If you could macro security would hardly be secure. What macros are in the workbook that is being opened?

 

[dmurphy1991]

it's a very large file with multiple macros in it. but im just trying to extract the finished/ final data from a specific sheet. technically the file im extracting from is 'finished' so wont need macros enabled as i wont be using any of them. i couldn't get a method of extrating the data without opening the file to open to work though.

 

[Andrew Poulsom]

You may be able to use a Query or ADO:

Copy a range from closed workbooks (ADO)