Share via

Method 'Open' of object Workbooks failed

Anonymous
2018-08-16T00:14:56+00:00

Hello,

I have a piece of code which has been running and working for a few months, however, all of a sudden it will not run and I receive the runtime 1004 error as above. The de-bugger shows that the issue is the line highlighted in red.

The code goes into all files in a folder, picks up the relevant tabs and copies these into a single tab in the master file. I have looked everywhere and can't seem to find an answer to this - one idea as to add the folder location to Excel's trusted locations, however, this has not worked.

Sub PullForecasts()

Set WBT = ThisWorkbook

Application.ScreenUpdating = False

WBT.Sheets("Forecast Master").UsedRange.Clear

Path = "C:\Users\OneDrive\Squad Tracking Folders\New Trackers"

Filename = Dir(Path & "*.xlsm")

Do While Filename <> ""

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

For Each ws In Worksheets

If Mid(ws.Name, 1, 16) = "Updated Forecast" Then

ws.Activate

ActiveSheet.UsedRange.Select

Selection.Select

Selection.Copy

Selection.PasteSpecial xlPasteValuesAndNumberFormats

Selection.Copy

Selection.Copy Destination:=WBT.Sheets("Forecast Master").Range("A" & Rows.Count).End(xlUp)

Application.CutCopyMode = False

End If

Next ws

     Workbooks(Filename).Close False

     Filename = Dir()

Loop

End Sub

Any help is greatly appreciated :)

Thanks!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

2 answers

Sort by: Most helpful
  1. Anonymous
    2018-08-16T06:36:37+00:00

    may be you do not have permissions to open it.

    in this case, try running the same code but files with locally on your PC

    eg: C:\users\yourprofile\desktop\mynewfolder\

    Was this answer helpful?

    3 people found this answer helpful.
    0 comments No comments
  2. Anonymous
    2018-08-16T07:11:27+00:00

    Hello,

    thanks for the reply hut it is definitely not that. It has been saved in the same place since we first started the process and is a file i created. I can go in and open them all manually but there are a lot and it would take a long time to collate them myself.

    thanks

    Was this answer helpful?

    0 comments No comments