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 stackoverflow.com/questions/579797/… — Gowire
– Gowire, Commented Jun 27, 2017 at 9:20
therefore the progress bar windows appear. What progressbar window? — Siddharth Rout
– Siddharth Rout, Commented Jun 27, 2017 at 9:31
@Siddharth Rout, Save progress, opening progress (files are large enough for it to appear). — Ans
– Ans, Commented Jun 27, 2017 at 9: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
– Siddharth Rout, Commented Jun 27, 2017 at 9: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
– Ans, Commented Jun 27, 2017 at 9:43

Gowire · Accepted Answer · 2017-06-27 09:33:35Z

1

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

answered Jun 27, 2017 at 9:33

Gowire

1,0707 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel vba hide file open and file save windows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest