0

My code loops over Excel files to open, read data and close.

There are some files with a warning popup when opened.

This warning is about some missing objects. I want to skip it and run normally.

I tried .DisplayAlerts and .ScreenUpdating with False.

could-not-load-warning-popup

Community
  • 1
  • 1
Tien King
  • 1
  • 1
  • 2
  • `read the data and close` If it is just that then I recommend using Oledb which is much faster and you will not face this issue. – Siddharth Rout Oct 28 '20 at 05:00
  • I don't know about OLEDB until you told me. But I read the lot of code about this and I will change my code to this way. Many thanks. – Tien King Oct 28 '20 at 06:49

1 Answers1

0

The second half of this answer has an example of using ADODB, much like using OLEDB that @SiddharthRout mentioned.

I think you could also create another instance of the Excel Application and then hide it. I don't believe that you get prompts on hidden windows (I could be wrong). This will definitely be slower then the above suggestion, but you won't have to change your existing code much, if at all.

Dim XlApp As Excel.Application
Dim Book As Workbook

Set XlApp = New Excel.Application
With XlApp
    .Visible = False ' Hide it, so that the end user can't see and prompts don't fire
    ' Open Workbooks and read sheets here...
    Set Book = .Workbooks.Open(Filename)
    With Book
        ' Read Sheet Data...
        .Close
    End With
    Set Book = Nothing
    .Quit
End With
Set XlApp = Nothing
Profex
  • 1,370
  • 8
  • 20
  • "Create another instance" is a much easier for me. But I need something faster because my code is already slow now. But I am not sure the different between ADODB vs OLEDB. I think I will change my code to this way. – Tien King Oct 28 '20 at 06:51
  • From [OLEDB-or-ADODB-Which-is-best-and-fastest](https://forums.codeguru.com/showthread.php?346279-OLEDB-or-ADODB-Which-is-best-and-fastest)...OLEDB is at a lower abstraction level than ADO. Therefore: OLEDB is faster, but takes a LOT more work and longer to program. ADO is fairly quick (but slightly slower than OLEDB). However development time is vastly better with ADO. Which is better? Can't really say. It really depends on your application. But generally unless you need unflagging speed... go for ADO. The learning curve is so much eaier – Profex Oct 28 '20 at 13:55