O365 migration breaks macro

[seiberr]

Hello again MrExcel,
You guys helped my with this script I inherited a few years ago. I am no VB expert so bear with me on this explanation. the below code snippet is from a macro that collects nmon data from multiple servers and creates a Summary workbook, to this it makes another sheet in the workbook that it makes a chart with average and max resource usage amounts. It has been a great time saver and we would hate to lose it. All that said let me just show you the sub:

Sub SetupPerfSummary()
'
' SetupPerfSummary Macro
'
' Keyboard Shortcut: Ctrl+s
  RemoveNmonFiles
  Application.ScreenUpdating = False
 
 'Use the Status Bar to inform  user of the macro's progress
 'change the cursor to hourglass
Application.Cursor = xlWait
 ' makes sure that the statusbar is visible
Application.DisplayStatusBar = True
 'add your message to status bar
Application.StatusBar = "Creating PerfSummary Tab..."
  Dim SummaryFile As String
  SummaryFile = SaveSummary
  Application.DisplayAlerts = False
  If Application.Version = "12.0" Then
    ActiveWorkbook.SaveAs FileName:=SummaryFile, FileFormat:=xlExcel8
  Else
    ActiveWorkbook.SaveAs FileName:=SummaryFile
  End If
  Application.DisplayAlerts = True
 
    Sheets.Add.Name = "Perf_Summary"
Application.StatusBar = "Generating PerfSummary Header..."

I do not show the whole sub as it is lengthy. I will post it if you think it necessary.
The line that the debugger marks is - "ActiveWorkbook.SaveAs FileName:=SummaryFile"
I tried to change this file and every time I do I get it to fail in a different spot. I think we recently discovered that if I make edits to this (I will do it from one of my team's desktops as they are still on version14).
So I think the vbscript is so old it wants to make an .xls and the version 16 which I am running with O365 does not like that format or is not backward compatible in some way.

Thanks,
Rob

 

[Kamolga]

Hi,

in O365 I usually use that code

[COLOR=#000000][FONT=Consolas]Set NewBook = Workbooks.Add 
[/FONT][/COLOR]Do 
    fName = Application.GetSaveAsFilename 
Loop Until fName <> False 
[COLOR=#000000][FONT=Consolas]NewBook.SaveAs Filename:=fName[/FONT][/COLOR]

to get the user to save a workbook where he wants the way he wants.

Otherwise, maybe you can replace the error line by

[COLOR=#333333]ActiveWorkbook.SaveAs FileName:=[/COLOR][COLOR=#333333]SummaryFile[/COLOR][COLOR=#333333] & [/COLOR][COLOR=#FF0000]".xlsx"[/COLOR][COLOR=#333333], FileFormat:=xlOpenXMLWorkbook[/COLOR]

 

[seiberr]

I made the edit :
I changed the SaveAs part to as you suggested:

ActiveWorkbook.SaveAs FileName:=

SummaryFile & ".xlsx", FileFormat:=xlOpenXMLWorkbook

Still fails, only now it appears to fail in an earlier part of the script:
>> Fails here << Set objFolder = objFSO.GetFolder(FPath)

Below I post the whole SaveSummary function and RemoveNmonFiles sub

Function SaveSummary()
'
' SaveSummary Macro
'
'
    
    Dim fNameDate As String
    Dim fNameTime As String
    Dim sheetName As String
    Dim FPath As String
    Dim FPathString As String
    Dim FName As String
   
    sheetName = Range("D2").Value
    fNameDate = splitName(sheetName, 2, ".")
    fNameTime = splitName(sheetName, 3, ".")
    FPathString = UCase(sheetName)
    FPath = splitName(FPathString, 1, "UNIX") & "Unix"
    FName = "Summary." & fNameDate & "." & fNameTime & ".xls"
    SaveSummary = FPath & "\" & FName
'    ActiveWorkbook.SaveAs Filename:=SaveSummary
End Function
Sub RemoveNmonFiles()

 
    Dim sheetName As String
    Dim FPath As String
    Dim FPathString As String
    Dim FName As String
   
    sheetName = Range("D2").Value
    FPathString = UCase(sheetName)
    FPath = splitName(FPathString, 1, "UNIX") & "Unix"
    
     Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(FPath)
    For Each objFile In objFolder.Files
    ''OLD
     '   If InStr(1, objFile.Name, ".xls", 1) = 0 Then
      '      If InStr(1, objFile.Name, "withTOP", 1) = 0 Then
       '         FName = "\" & objFile.Name
        '        Kill (FPath & FName)
         '   End If
            
        'End If
    '' END OLD
        If InStr(1, objFile.Name, ".nmon", 1) = 1 Then
            If InStr(1, objFile.Name, "withTOP", 1) = 0 Then
                FName = "\" & objFile.Name
                Kill (FPath & FName)
            End If
            
        End If
    
    Next
'    FName = "\*.nmon"
 '   If FileThere(FPath & FName) Then
  '  Kill (FPath & FName)
   ' End If
    
  End Sub

Oh and might help to see what the end product files look like.

Processed excel sheets look like:
servername.date.time.nmon

Raw nmon data which are CSV files:
servername.date.time

Summary page:
Summary.date.time

 

[Norie]

Rob
Is the function SaveSummary definitely returning a valid path and filename?

What error messages are you getting?

In particular what's the error message from this?

Set objFolder = objFSO.GetFolder(FPath)

 

[seiberr]

Oh sorry, it says path not found and when I hover over FPath it says Unix, which is the in the end of every path we store these nmon files in:
It stors on a share drive like Y:/projectname.date.time/Unix

 

[seiberr]

Seems everytime I edit, it dies in a different place, I started killing every excel process off whenever it exited badly.

I did finally get it to consistently fail at this line:

ActiveWorkbook.SaveAs FileName:=SummaryFile & ".xlsx", FileFormat:=xlOpenXMLWorkbook

I have tried many iterations of this line, I noticed it was naming the file "Summary.20170330.1559.xls.xlsx" (even when it dies here it still creates the file)

ActiveWorkbook.SaveAs FileName:=SummaryFile, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.SaveAs FileName:=SummaryFile
ActiveWorkbook.SaveAs FileName:=SummaryFile, FileFormat:=xlExcel8