0

I have a script which opens an external file to execute a script and after finishing the script, it saves as an xlsb file. The code worked perfectly for saving from xlsb file to xlsb file. But because I want to have some security for the original file, I want the original xlsb file to be a xltm file. I adapted the save as script to it and tested it as a separate routine in the file itself. It works perfectly.

Now I added the routine to the external script file, but when I execute the script then, Excel freezes and no 'save as' screen appears at all. I expect the external script to have some other external reference to work properly but I cannot figure out how to adjust my script to this.

This is the code of the original file:

Option Explicit
Sub vernieuwalles()
Dim myTemplate As String: myTemplate = ActiveWorkbook.Name
Dim myTool As String: myTool = "refresh_segment_template.xlsm"

  Application.ScreenUpdating = False

  Workbooks.Open GetPath & myTool
  Application.Run myTool & "!vernieuwalles", myTemplate

  Call Windows(myTool).Close(False)

  Application.ScreenUpdating = True

End Sub
Private Function GetPath() As String
Dim myPosition As Integer
Dim myPath As String: myPath = ActiveWorkbook.Path

  myPosition = InStr(StrReverse(myPath), "\") - 1
  myPosition = Len(myPath) - myPosition

  GetPath = Mid(myPath, 1, myPosition - 1) & "\XLAM\"

End Function

It refers to the external script file to be opened and executed.

The external file has this script (I only pasted the save as part of the script):

Option Explicit

Dim aantalrijen As Long

Const SheetSchaduwblad As String = "schaduwblad"
Sub vernieuwalles(mytemplate As String)

  Windows(mytemplate).Activate

  On Error GoTo Err_

  Application.StatusBar = "Bezig met vernieuwen"

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

'  Call SheetOpschonen

  Call datawissen
  Call dataplaatsen
  Call kolomtitels
  Call toevoegen
  Call maaktabel
  Call refreshpivots

Exit_:
  Application.StatusBar = ""
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Exit Sub

Err_:
  Call MsgBox(Err.Number & vbCrLf & Err.Description)
  Resume Exit_

Application.Calculation = xlCalculationAutomatic

End Sub

Sub refreshpivots()
Dim workbook_Name As Variant
Dim location As String
Dim workbookdirectory As String
Dim activewb As String

ActiveWorkbook.RefreshAll

activewb = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)

workbookdirectory = "M:\Commercie\Marktdata\IRi\Segment Ontwikkeling\"

workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), *.xlsb", InitialFileName:=workbookdirectory & activewb)

If workbook_Name = False Then

ActiveWorkbook.SaveAs filename:=activewb, FileFormat:=50

End If

But when I execute the script from the original file, excel freezes as pointed out. What should be changed for this code to be working with the external file and the save from xltm to xlsb script?

As pointed out: the save as script is the only thing that has changed in the script and the xlsb to xltm extension-change is the only thing that has changed in the original file.

DutchArjo
  • 319
  • 3
  • 8
  • 29
  • `ActiveWorkbook.SaveAs filename:=activewb, FileFormat:=50` Debug this line and check what is the value of `activewb` – Siddharth Rout Feb 15 '19 at 12:41
  • I changed activewb to mytemplate.name (mytemplate is the original file) as it is referred to in the script and tried to execute the script again. But still excel freezes. – DutchArjo Feb 15 '19 at 12:49
  • I have run into this as well with projects im working on and I found that adding `DoEvents` before and/or after the save event seemed to help. The `DoEvents` allows Excel to complete what its doing before the save event and got rid of the freezing issue i ran into. – Zack E Feb 15 '19 at 13:20
  • Where do you suggest I should add that doevents in the script? However, I suspect the activewb.name or mytemplate.name part to be the problem as it might not be able to read the filename of the source file the way I made the script? – DutchArjo Feb 15 '19 at 14:37

0 Answers0