CODEWITHSUNDEEP
vbaexcel
tbowden
tbowden

Reputation: 1058

I have written a macro using Application.ScreenUpdating, however it is not working

The below code should hide the workbook that it is opening and run the macro then close the workbook, all the while not showing the user anything.

What mistake have I made? Also is there an easier way to take a value written in "Sheet 1" and put it in a cell in "QR Code"? Thanks

Sub Open_Fill_Close()

Application.ScreenUpdating = False

    Worksheets("Sheet1").Select
    invoiceNo = Range("A1")

    Set myData = Workbooks.Open("HD:Users:User1:Desktop:QR.xlsm")

    Worksheets("QR Code").Select
    Worksheets("QR Code").Range("Y39").Select
    RowCount = Worksheets("QR Code").Range("Y39").CurrentRegion.Rows.Count
    With Worksheets("QR Code").Range("Y39")
    .Offset(RowCount, 0) = invoiceNo
    End With

    Application.Run "QR.xlsm!ExportCellsAsPicture"

    Workbooks("QR.xlsm").Close False

Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 303

Answers (3)

Daniel Möller
Daniel Möller

Reputation: 86650

ScreenUpdating is not meant to make the application invisible. It's meant to freeze the screen while your code runs. So it runs much faster. (If you forget to enable it again, of if your code crashes before hitting the line where you enable it, your Excel will not respond anymore)

In order to open a workbook invisibly, you will need to create a new application and make that application invisible (it's already invisible by default)

Dim App as Application     
Set App = new Application
Set myData = App.Workbooks.Open("HD:Users:User1:Desktop:QR.xlsm")

Then work the rest of your code. In this case, if you forget to App.Quit() or if your code crashes before quitting the application, you will get a running process on the background, consuming your computer's memory.

If at some point you want to make that app visible: App.Visible = true.
To make it back to invisible: App.Visible = false.

Whole code:

Sub Open_Fill_Close()




    Worksheets("Sheet1").Select
    invoiceNo = Range("A1")

    Dim App as Application     
    Set App = new Application
    Set myData = App.Workbooks.Open("HD:Users:User1:Desktop:QR.xlsm")

    myData.Worksheets("QR Code").Select
    myData.Worksheets("QR Code").Range("Y39").Select
    RowCount = myData.Worksheets("QR Code").Range("Y39").CurrentRegion.Rows.Count
    With myData.Worksheets("QR Code").Range("Y39")
    .Offset(RowCount, 0) = invoiceNo
    End With

    App.Run "QR.xlsm!ExportCellsAsPicture"

    App.Workbooks("QR.xlsm").Close False
    App.Quit

End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

This should open the workbook, deal with it and subsequently close it without the user being aware of changes to the screen.

Sub Open_Fill_Close()
    Dim wsq As Worksheet, wb As Workbook

    Application.ScreenUpdating = False

    With Worksheets("Sheet1")

        Set wb = Workbooks.Open("HD:Users:User1:Desktop:QR.xlsm")
        Set wsq = wb.Worksheets("QR Code")
        wsq.Cells(Rows.Count, "Y").End(xlUp).Offset(1, 0) = .Range("A1").Value

        'Application.Run "QR.xlsm!ExportCellsAsPicture"  'Do not know how this will be affected

        wb.Close False

    End With

    Application.ScreenUpdating = True

End Sub

I'm unclear on why you are assigning a value to the bottom of the Y column and closing without saving but I would suppose that the macro being run accomplishes.

Upvotes: 0

MatthewD
MatthewD

Reputation: 6801

After you open it.

Application.Interactive = False
Application.Visible = False

Upvotes: 1

Related Questions