Excel 2007 : Auto Open File with Macro

**OrangeBoy** · 10-14-2011, 04:41 AM

Hi there,

Having trouble with this code ATM,

Sub WEEKLY_UPDATE()

    Application.ScreenUpdating = False

 

    Dim ThisPath As String

    ThisPath = "C:\DD\WW\FOLDER\"

    Workbooks.Open ActiveWorkbook.ThisPath & "" & Sheet1.TextBox2.Value

 

    Windows(Sheet1.TextBox2.Value).Activate

    Sheets("Hello").Select

    Range("EA2:EG12").Select

    Selection.Copy

    Windows(Sheet1.TextBox1.Value).Activate

    Range("B2").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

Basically, text box 2 is where I will key in the file name to open. However, I am getting this error,

"Object doesn't support this property or method"

All I want is before the macro starts to run, it will open up the file that I want. In addition, the path folder of the file must be prefix. So all I need is to change the text box value to the file name.

Please help, thanks.

**TMS** · 10-14-2011, 05:18 AM

It's difficult to test something like this without the workbook, worksheet(s), object(s), code, files to open, etc.

Maybe you could upload a sample workbook and file(s) to test it with?

Regards

**Andrew-R** · 10-14-2011, 05:23 AM

ThisPath is a variable, it's not a property of the ActiveWorkbook object.

Also you're not opening the workbook in TextBox1 - is that workbook already open? If so there's probably a much easier way to do this.