Import .txt macro runs from Excel but not when executed from an external vbscript.

[Nachtschade]

I have this macro:

Sub getpathnp()
Application.ScreenUpdating = False
    Dim wbExcel As Workbook, wbText As Workbook
    Dim wsExcel As Worksheet
    Set wbExcel = ThisWorkbook 'specify here which Excel file the text file’s content is to be pasted into
    Set wsExcel = wbExcel.Sheets("Timeplay") 'specify here which worksheet to use
    Set wbText = Workbooks.Open("A:\nppath.txt") 'add here the path of your text file
 
    wbText.Sheets(1).Cells.Copy wsExcel.Cells
    On Error Resume Next
    
    
    wbText.Close SaveChanges:=False
    
    If Sheets("Timeplay").Range("A1") = Sheets("Invoer TP").Range("A1") Then
    
      
    Call TimePlay

Else

I found this and modified it to my use when I was googling for a way to have Excel import the contents of a .txt file.

This macro is working fine when I execute it from Excel.

However I am trying to execute it using a vbscript:

Option Explicit
Dim xlApp, xlBook
Set xlBook = GetObject("A:\test.xlsm")
Set xlApp = xlBook.Application
xlApp.Run "getpathnp"

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Quit

The script does what it should do. It runs the macro, but the macro doesn't copy the content of the txt file into Excel the way it does when I run from Excel itself.

Does anyone what the problem is here?

Is it some kind of focus thing? Or maybe something with admin rights?

 

[Dan_W]

The macro code you've provided is incomplete. Out of curiosity, how do you know the VBScript runs the Macro?
One thing that jumps out at me is the use of:

Application.ScreenUpdating = False

and

    On Error Resume Next

which may both/either be part of the problem, but again, without seeing the rest of the code, I'm simply guessing...

 

[Nachtschade]

Indeed, I forgot some of the code. Sorry.

The reason I know it was run is because part of the macro was executed and I could see the results.

Meanwhile, after much testing I found out what the issue was. Apparently there was no Application.ScreenUpdating = True at the end of the macro (so your suggestion was certainly valid, thnx), which doesn't matter for it being run from inside Excel but does matter when ran externally. I'm not entirely sure why this is so but I have now made it work the way I want to regardless.

 

[Dan_W]

I had a feeling that might be the case (as I indicate above ), but didn't want to assume, seeing as I didn't know how the subroutine ended. Forgetting to turn it back on can lead people to think that nothing has happened, or that Excel has stalled, etc. Ordinarily, you would want to put line of code switching it back to True at the end of the routine (and preferably in the Error handling routine, if there is one). Glad you managed to solve it.

 

[Nachtschade]

During testing with similar but different macro's I've actually indeed had Excel freeze completely (had to kill it via taskmanager) on me because of the lack of Application.ScreenUpdating = True in the code while executing the macro from inside Excel worked fine regardless of whether Application.ScreenUpdating = True was there or not.

 

[Dan_W]

Perhaps your situation was different, but ordinarily, ScreenUpdating = False only makes Excel seem like it has stalled (clicking on cells are non reponsive, values are not updated etc), but it hasn't in fact stalled. I note that your VBScript requires that the workbook is already open when it's run - is that by design? It's possible to instantiate a new instance of Excel via VBScript if that's what you need.

 

[Nachtschade]

Perhaps your situation was different, but ordinarily, ScreenUpdating = False only makes Excel seem like it has stalled (clicking on cells are non reponsive, values are not updated etc), but it hasn't in fact stalled.

I am unsure what the difference is in practice, can you elaborate?

I note that your VBScript requires that the workbook is already open when it's run - is that by design? It's possible to instantiate a new instance of Excel via VBScript if that's what you need.

Most definitely by design.

 

[Dan_W]

When the application stalls, it is in fact unresponsive for whatever reason. As far as I am aware, when the ScreenUpdating is set to False, the worksheet is actually just not being redrawn. If you have executed a macro which would fill a range of cells with values, but (owing to ScreenUpdating = False) you look at the worksheet and there is nothing there, that's only because the screen has not been redrawn - the cells do contain values, and you can still interact with Excel (albeit in a limited way - e.g., changing worksheets, reenabling ScreenUpdating by setting it to True, etc).

 

[Nachtschade]

I didn't see any way to interact with Excel and everything in the spreadsheet was visually empty. So it definitely looked frozen. I did not check every possibility though. I happen to save my work often so I just go back when something breaks or I lose track of whatever I'm doing. Regardless; the more interesting thing is why this happens when the macro is executed externally but not when it is done internally.