0

I have an Excel VBA macro that I run daily which opens excel files from an internal filepath and pastes the data into my main file. This macro will look to pull multiple different reports. However, if one report does not exist, I get a popup message that states that the file could not be opened (sometimes files are posted late or are incorrectly named). This popup can hold up the entire macro run (ie. if the first file causes an issue, the other files will not be pulled in). Ideally, I want this message not to popup or have a way to automatically click "OK" and then head to an error handling sheet. I've tried turning EnableEvents off and DisplayAlerts off, but have not had any luck as I still get this popup if the file does not exist or is misspelled. Simplified code below where the file will show the popup on "Set oWB = oExcel.Workbooks.Open("http://redactedfilepath.csv")"

Popup Error

Sub TestFileConnection

Dim oExcel As Excel.Application
Dim oWB As Workbook

Set oExcel = New Excel.Application


Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set oWB = oExcel.Workbooks.Open("http://redactedfilepath.csv")

oWB.Sheets("Sheet1").UsedRange.Copy

ThisWorkbook.Sheets("MainFileSheet1").Cells(1, 1).PasteSpecial xlPasteValues
oWB.Application.CutCopyMode = False
oWB.Close False
Set oWB = Nothing



Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub
Improve this question
5
  • Have you looked into this? Commented May 15, 2017 at 18:27
  • 1
    VBA Check if file (from website) exists Commented May 15, 2017 at 18:30
  • I was going to use "check if file exists" approach, but I was hoping there would be an easier way since I pull over 100+ files. Commented May 15, 2017 at 18:33
  • Could you explain your problem with this approach? Commented May 15, 2017 at 18:45
  • If you have 100+ files, why not store the filenames in an array and loop through them. That way 'check if file exists' is one line of code. Or you can 'on error resume next' perhaps? Or on error goto 'next file'? Commented May 15, 2017 at 20:04
Related questions
2
Prevent File Now Available Popup Excel
0
Prevent Excel pop up message in VB code
3
Turn Off Msg for Update Links for Excel Spreadsheet upon Opening
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.