• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel (Office 365) Issues after latest Office 365 update|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Excel (Office 365) Issues after latest Office 365 update|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Excel (Office 365) Issues after lat…
sp_PrintTopic sp_TopicIcon
Excel (Office 365) Issues after latest Office 365 update
Avatar
Hans Hallebeek
the Netherlands
Member
Members
Level 0
Forum Posts: 69
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
1
April 3, 2019 - 5:18 am
sp_Permalink sp_Print

Hi this is a general question about 'sudden' issues some users are running into after the latest Office 365 update. The reaso I know it's due to this is because one of the people I wrote an Excel application was using it yesterday without problems and her PC was updated early this morning and it no longer works.

It happens when the shell command is run that I use to list a number of files to a temporary file written to the user's temp folder:

[code]
If FileExists(Environ$("TEMP") & "\tempfilelist.lst") = True Then Kill Environ$("TEMP") & "\tempfilelist.lst"
Call Shell("cmd /C dir """ & Replace(tPath & "\" & tName, "\\", "\") & """ /B > " & Environ$("TEMP") & "\tempfilelist.lst", vbMinimizedNoFocus)
[code/]

You can rest assured that the variables are all correct and when I run it directly from the command prompt no problem but in the VBA code if fails

The first line KILL works, the file is removed, but the Call Shell line throws the error

The Error message is Error 5 invalid procedure or invalid argument (freely translated from the Dutch message box

Apart from that, but of course not everything can be tested now it seems it runs Okay, 

Have any of you encountered problems since let's say a week or two?

I asked their network engineer to look at it and he noticed that the Office updates started March 29 and it was just her system that was updated this morning

I'me very curious and worried, I did not read anything on any blog or site about Office 365 modifications to the good old MS-DOS commands

Will need to write an alternative code for itCryCryCry

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
April 3, 2019 - 1:15 pm
sp_Permalink sp_Print

Hi Hans,

I couldn't execute your code as-is because it's not complete - I don't have the variables you are using set to any values.

But if I just try Call Shell("cmd /C dir ") it works fine.  I tried this on Excel 2013 and in 365.

If you try to execute just Call Shell("cmd /C dir ") does that work for you?

Regards

Phil

Avatar
Hans Hallebeek
the Netherlands
Member
Members
Level 0
Forum Posts: 69
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
3
April 3, 2019 - 5:19 pm
sp_Permalink sp_Print

Hi Phil, 

Thanks for your answer
I understand you don't have the variables, but that's not the issue, the fact is that under Office 365 Business the code no longer works.

This is what the full string looks like:

[code]

'the line in the VBA code

Call Shell("cmd /C dir """ & Replace(tPath & "\" & tName, "\\", "\") & """ /B > " & Environ$("TEMP") & "\tempfilelist.lst", vbMinimizedNoFocus)

the actual string executed:
cmd /C dir "S:\HOUTROT\Datalogs\*-houtrot-dataverwerking-projectlogs.xlsx" /B > C:\Users\keebe\AppData\Local\Temp\tempfilelist.lst

[/code]

I know how this works and I've been using this module since 2010 and the person using my application had no problems on Monday (April 1st and no Fool's day)

The Office Update on her system took place Tuesday morning early and when she wanted to work with the file ran  into this issue. I also posted on the Microsoft site but 'til now silence.

I wrote an alternative code for this avoiding the use of the file created in the temp folder and happliy it works but now I just want to know if thsi a goof-up by Microsoft or is it actually another functionality which is no longer use able as other several years ago that 'suddenly' where no longer available.

Avatar
Hans Hallebeek
the Netherlands
Member
Members
Level 0
Forum Posts: 69
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
4
April 3, 2019 - 7:43 pm
sp_Permalink sp_Print

This is the full code.

GetProjectlogBestanden is the modified version that I rewrote so that it works

GetProjectlogBestanden2 is the original code that fails with Office 365 Business

[code]
Public Function GetProjectlogBestanden(tPath As String, tName As Variant) As Variant
Application.StatusBar = "Even geduld aub ..."
Dim wsM As Worksheet
Dim dtDelay As Date
dtDelay = Now
Dim File As Integer
Dim isPresent As Boolean
Dim rng As Range
Dim TextOfLine As String, myArr()
Dim vFile As String
Dim i As Integer
tPath = TrailingSlash(tPath)
vFile = Dir(tPath & tName)
i = 0
If vFile <> "" Then
Do
TextOfLine = Left(vFile, InStr(1, vFile, "-Houtrot") - 1)
If LCase(TextOfLine) <> "sjabloon" Then
i = i + 1
ReDim Preserve myArr(1 To i)
myArr(i) = TextOfLine
End If
vFile = Dir()
Loop While vFile <> ""
End If
Application.StatusBar = Captiontxt
If i = 0 Then i = i + 1: ReDim myArr(1 To i): myArr(i) = "geen Project Logbestanden aanwezig"
GetProjectlogBestanden = IIf(i <> 0, myArr, vbNullString)
End Function

Public Function GetProjectlogBestanden2(tPath As String, tName As Variant) As Variant
Application.StatusBar = "Even geduld aub ..."
Dim wsM As Worksheet
Dim dtDelay As Date
dtDelay = Now
Dim File As Integer
Dim isPresent As Boolean
Dim rng As Range
Dim TextOfLine As String, myArr()
Dim vFile As String
Dim i As Integer
If FileExists(Environ$("TEMP") & "\tempfilelist.lst") = True Then Kill Environ$("TEMP") & "\tempfilelist.lst"
Call Shell("cmd /C dir """ & Replace(tPath & "\" & tName, "\\", "\") & """ /B > " & Environ$("TEMP") & "\tempfilelist.lst", vbMinimizedNoFocus)
WaitForIt:
Application.Wait dtDelay + TimeSerial(0, 0, 2)
If FileExists(Environ$("TEMP") & "\tempfilelist.lst") = False Then GoTo WaitForIt

File = FreeFile
Open Environ$("TEMP") & "\tempfilelist.lst" For Input As File
i = 0
While Not EOF(File)
Line Input #File, TextOfLine
TextOfLine = Left(TextOfLine, InStr(1, TextOfLine, "-Houtrot") - 1)
If LCase(TextOfLine) <> "sjabloon" Then
i = i + 1
ReDim Preserve myArr(1 To i)
myArr(i) = TextOfLine
End If
Wend
Close File
Kill Environ$("TEMP") & "\tempfilelist.lst"
Application.StatusBar = Captiontxt
If i = 0 Then i = i + 1: ReDim myArr(1 To i): myArr(i) = "geen Project Logbestanden aanwezig"
GetProjectlogBestanden2 = IIf(i <> 0, myArr, vbNullString)
End Function
[/code]

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
5
April 3, 2019 - 9:32 pm
sp_Permalink sp_Print

Hi Hans,

Neither of these functions work as-is because Captiontxt is not defined, neither is the FileExists function.  If you could supply the actual workbook with data, sample function calls and exactly what you are expecting as an outcome it'll makes things a lot easier for me.

It looks like you are testing if the file tempfilelist.lst exists, and if it does then you are deleting it.

The Shell call is then doing a dir listing and output is redirected into tempfilelist.lst

As you are using a single > for redirecting the dir command output, the contents of tempfilelist.lst are overwritten, you don't need to delete it before hand.

If you were using >> to redirect output, the file contents are appended.

I'm guessing at exactly what you are trying to do but it looks like you are listing a file type specified by tName (e.g. *.log) and then trying to read the dir listing of all those files from the tempfilelist.lst

I changed both functions to subs for testing and the Shell call did start a command prompt in Excel 2013, but the dir didn't seem to work.

The rewritten function using Dir() is a better solution and worked for me so I'd stick with that rather than spending more time debugging the old Shell version.

Regards

Phil

Avatar
Hans Hallebeek
the Netherlands
Member
Members
Level 0
Forum Posts: 69
Member Since:
October 17, 2018
sp_UserOfflineSmall Offline
6
April 3, 2019 - 11:40 pm
sp_Permalink sp_Print

Forgot about the Function Captiontxt, sorry, that's nothing, the TempFileList.lst is the files name and extension the log is part of the xlsx filename.

Yess I know the difference between the > and >> but I need the > and to make sure I kill the file if it exists before anyway.

What bugs me is the Microsoft either doesn't  have enough testers or just drop bombs and don't notify when updates come out, probably hidden somewhere deep in small type, but still.

It's awfully quiet on the Microsoft forum, no replies or anything on my question.

Thanks for looking anyway.

Cheers,

Hans

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
7
April 4, 2019 - 8:50 am
sp_Permalink sp_Print

Hi Hans,

Unfortunately things like this do occur.  Only yesterday I had to rewrite code that had worked previously for embedding workbooks in a website.  Because MS had changed the underlying way they allowed you to embed workbooks 🙁

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Ben Hughes, Dario Serrati, Christopher Anderson, dectator mang, Oluwadamilola Ogun, yashal minahil
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27181

 

Member Stats:
Guest Posters: 49
Members: 31858
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.