Excel vba hide file open and file save windows

Question

I hava a macros that opens, edits and saves files. But it takes some time, therefore the progress bar windows appear. I don't know how to get rid of them. I tried:

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.DisplayAlerts = False

But it doesn't work. Any advice?

EDIT: Here is my code:

I tried the suggested answer here, but it doesn't work (it all just slows down and the output folder is empty). Maybe I'm doing it's wrong?

Sub iterateThroughFolder()
    '**SETTINGS**
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.StatusBar = "Starting..."

    '**VARIABLES**
    Dim folderPath As String
    folderPath = "Y:\vba\test_reserves\test_data\"

    '**INVISIBLE APPLICATION**  <---- the part from other answer
   ' Dim app As New Excel.Application
    Dim app As Object
    Set app = CreateObject("Excel.Application")
    app.Visible = False

    '**LOOPING THROUGH THE FOLDER**
    fileTitle = Dir(folderPath & "*.xl??")
    Do While fileTitle <> ""
        'SETTINGS
        Application.DisplayAlerts = False
        Application.StatusBar = fileTitle + "pending: "

        'OPENING FILES
        Dim resultWorkbook As Workbook
        Dim dataWorkbook As Workbook

        'OLD VARIANT:
        'Set resultWorkbook = 
           'Workbooks.Open("Y:\vba\test_reserves\files\rating_template.xls")
        'Set dataWorkbook = Workbooks.Open(folderPath & fileTitle)

        'NEW VARIANT:
        'Set resultWorkbook = app.Workbooks.Add("Y:\vba\test_reserves\files\rating_template.xls")
       ' Set dataWorkbook = app.Workbooks.Add(folderPath & fileTitle)

       'NEXT NEW VARIANT (from this question's answer):
       Set resultWorkbook =app.Application.Workbooks.Open
         ("Y:\vba\test_reserves\files\rating_template.xls ")
       Set dataWorkbook = app.Application.Workbooks.Open(folderPath & 
         fileTitle)

        'REFRESHING CONNECTIONS (?)
        Dim cn As WorkbookConnection
        For Each cn In resultWorkbook.Connections
                cn.Refresh
        Next

        'GETTING THE NAME AND PUTTING IT IN "A1" cell of "B1" list
        Application.StatusBar = Application.StatusBar + "Getting the state name"
        Dim stateName As String
        stateName = dataWorkbook.Worksheets("ðàçäåë 1").Cells(5, 4).Value
        resultWorkbook.Worksheets("B1").Cells(1, 1).Value = stateName


       'SAVING AND CLOSING
        Application.StatusBar = Application.StatusBar + "Saving and closing new rating file"
        resultWorkbook.SaveAs Filename:="Y:\vba\test_reserves\output\" + stateName
        resultWorkbook.Close
        dataWorkbook.Close
        Application.DisplayAlerts = True

        'NEXT FILE
        Application.StatusBar = Application.StatusBar + "Getting next data file"
        fileTitle = Dir()
    Loop
    '**CLEANING THE APP**  <--- from another answer, added it, so it doesn't
    'work now
    app.Quit
End Sub

Have you tried this question https://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display — Gowire, Jun 27 '17 at 09:20
`therefore the progress bar windows appear.` What progressbar window? — Siddharth Rout, Jun 27 '17 at 09:31
@Siddharth Rout, Save progress, opening progress (files are large enough for it to appear). — Ans, Jun 27 '17 at 09:34
If the files are large then isnt it good then it shows that? If I was a user and I didn't have a clue what was happening, I would really get frustrated. :) — Siddharth Rout, Jun 27 '17 at 09:41
@Siddharth Rout, There are many files and it's more frustrating to constantly pay attention to pop up windows... I'd rather turn on the macros and do my stuff... It's macros for me and colleagues, so we'd all have clue what's going on.. (Maybe even through Application.StatusBar, that would be enough)... — Ans, Jun 27 '17 at 09:43
@Gowire, Hello, I tried it. But it doesn't seem to work. I posted my code here, maybe you could look at it? — Ans, Jun 27 '17 at 09:49

score 1 · Answer 1 · answered Jun 27 '17 at 09:33

Here's some code you can try... It starts a hidden Excel application and opens a workbook. You can work with this workbook using the WB object.

However... you will still se a wait mouse icon if the operation takes time (hour glass or rotating cursor).

Dim ExcelApp As Object
Dim WB As Workbook

Set ExcelApp = CreateObject("Excel.Application")
Set WB = ExcelApp.Application.Workbooks.Open("myfile.xlsx")

WB.DoYourStuffWithTheWorkbook

WB.Save
WB.Close

Excel vba hide file open and file save windows

1 Answers1