Tag Archives: Microsoft

Batch Converting Excel XLS files to XLSX

A little while back I posted a macro to batch covert Visio VSD files to VSDX files which got a decent number of people messaging me. Recently I found how many excel files we had using the old format which just like old Visio files take up a lot of extra space. So I went through and modified my Visio converter over for Excel. So here is a step by step to write your own Excel file converter:

  1. Open a new Excel document. Save it as a “Excel Macro-Enabled Workbook (*.xlsm)
  2. In the first cell put something like “To run the conversion hit ALT+F11 to open the program then F5 to run it”.
  3. Hit ALT+F11 to open up the Microsoft Visual Basic for Applications screen
  4. Right click “ThisWorkbook” at the top left then Insert -> Module
  5. In the module copy and paste the following in:
Public FilesAttempted As Integer
Public FilesConverted As Integer
Public FilesDeleted As Integer
Public FilesSkipped As String

Sub ConvertToXlsx()
FilesAttempted = 0
FilesConverted = 0
FilesDeleted = 0
FilesSkipped = ""
Dim FileSystem As Object
Set FileSystem = CreateObject("Scripting.FileSystemObject")

Dim HostFolder As String
Dim DeleteOriginal As Boolean
Dim RemovePersonal As Boolean

''' HostFolder is directory to start at.  Change to your base directory.
HostFolder = "C:\temp"

''' DeleteOriginal will delete the original file as long as the xlsx was created.  Either True or False
DeleteOriginal = False

DoFolder FileSystem.GetFolder(HostFolder), DeleteOriginal

MsgBox "Conversion complete! " & vbCrLf & vbCrLf & "Files attempted: " & FilesAttempted & vbCrLf & "Files converted: " & FilesConverted & vbCrLf & "Files deleted: " & _
    FilesDeleted & vbCrLf & "Files with issues: " & vbCrLf & FilesSkipped, vbOKOnly + vbInformation, "Conversion Complete"
  
End Sub

Sub DoFolder(Folder, DeleteOriginal)
  On Error GoTo ErrHandler:
  Dim SubFolder
  For Each SubFolder In Folder.SubFolders
    DoFolder SubFolder, DeleteOriginal
  Next
  Dim File
  Dim myWorkbook As Workbook
  For Each File In Folder.Files
    ' For each file name sure its a xls and not a temp file
    If ((Right(File, 3) = "xls") And (Right(File, 4) <> "~xls")) Then
      FilesAttempted = FilesAttempted + 1
      ' Open the file
      Set myWorkbook = Workbooks.Open(File)
       
      ' Save as a xlsx and increase our counter
      myWorkbook.SaveAs Filename:=File & "x", FileFormat:=xlOpenXMLWorkbook
      myWorkbook.Close (False)
      FilesConverted = FilesConverted + 1
      
      ' Delete the original if set and the new xlsx exists
      If ((DeleteOriginal = "True") And (FileExists(File & "x"))) Then
        SetAttr File, vbNormal
        Kill File
        FilesDeleted = FilesDeleted + 1
      End If
NextFile:
    End If
  Next
Done:
  Exit Sub
  
ErrHandler:
Debug.Print "Error encountered.  Error number: " & Err.Number & " - Error description: " & Err.Description
  If File <> "" Then
    FilesSkipped = FilesSkipped & File & vbCrLf
    GoTo NextFile:
  End If
    
End Sub

Function FileExists(ByVal FileToTest As String) As Boolean
   FileExists = (Dir(FileToTest) <> "")
End Function

Change the HostFolder to the directory you want to run this on and hit F5 to run. It will open each Excel workbook with a xls extension in that directory, and all sub directories, then save it as a xlsx. If you want it to automatically delete the old xls file change the DeleteOriginal variable to True or just manually delete them after conversion.

Resetting Windows Update on Domain Joined Computers

Windows Update & WSUS have been a thorn in my side for many many years. When it works its great but when it doesn’t it can be very frustrating to figure out what went wrong. Over the years I’ve had to rebuilt WSUS twice, once when it just stopped pushing updates and another time where it imploded itself and corrupted the database. Recently we had a number of computers, around 10%, stop reporting back to WSUS for status. They also reported no updates available when checking for updates using WSUS. We tried all the troubleshooters, DISM cleanups, etc, but nothing seemed to work. I thought maybe it was WSUS again but that wouldn’t make sense with so many successfully getting updated. Even tried the Microsoft recommendations on resetting Windows Update but in our case BITS didn’t want to stop. And related to this we were getting Task Host errors on shutdown for those machines with the reason being “AutomaticUpdateHost” which would make sense if BITS was stuck.

After some testing we found that the Microsoft recommendations did work when in safe mode. Problem was doing this as easily with the least amount of downtime and hands on touching. To that end I created a series of three batch files. The first one, run as a administrator, will set the boot options to safeboot with networking and reboot:

bcdedit /set {default} safeboot network
shutdown -r -t 5

The second is most of the Microsoft recommendations along with others I’ve found on the internet:

@echo off
echo Stopping Windows Update and BTIS services…
net stop bits /y
net stop wuauserv /y
net stop appidsvc /y
net stop cryptsvc /y
echo Killing any windows updates in process
taskkill /im wuauclt.exe /f
echo Deleting some files…
del /s /q /f "%ALLUSERSPROFILE%\Application Data\Microsoft\Network\Downloader\qmgr.dat" del /s /q /f "%ALLUSERSPROFILE%\Microsoft\Network\Downloader\qmgr.dat"
del /s /q /f "%SYSTEMROOT%\WindowsUpdate.log"
rd /s /q "C:\WINDOWS\SoftwareDistribution"
rd /s /q "%SYSTEMROOT%\system32\Catroot2"
del %USERPROFILE%\AppData\Local\Temp* /s /q
for /d %%x in (%USERPROFILE%\AppData\Local\Temp*) do @rd /s /q "%%x"
del %systemroot%\Temp* /s /q
for /d %%y in (%systemroot%\Temp*) do @rd /s /q "%%y"
echo Reset the BITS service and the Windows Update service to the default security descriptor.
sc.exe sdset bits D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)
sc.exe sdset wuauserv D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;AU)(A;;CCLCSWRPWPDTLOCRRC;;;PU)
echo Deleting registry keys….
reg delete "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v AccountDomainSid /f
reg delete "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v PingID /f
reg delete "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v SusClientId /f
reg delete "HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate" /v SusClientIDValidation /f
REG DELETE "HKLM\Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update" /v LastWaitTimeout /f
REG DELETE "HKLM\Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update" /v DetectionstartTime /f
Reg Delete "HKLM\Software\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update" /v NextDetectionTime /f
echo Re-registering Windows Update components…
regsvr32.exe /s c:\windows\system32\atl.dll
regsvr32.exe /s c:\windows\system32\urlmon.dll
regsvr32.exe /s c:\windows\system32\mshtml.dll
regsvr32.exe /s c:\windows\system32\shdocvw.dll
regsvr32.exe /s c:\windows\system32\browseui.dll
regsvr32.exe /s c:\windows\system32\jscript.dll
regsvr32.exe /s c:\windows\system32\vbscript.dll
regsvr32.exe /s c:\windows\system32\scrrun.dll
regsvr32.exe /s c:\windows\system32\msxml.dll
regsvr32.exe /s c:\windows\system32\msxml3.dll
regsvr32.exe /s c:\windows\system32\msxml6.dll
regsvr32.exe /s c:\windows\system32\actxprxy.dll
regsvr32.exe /s c:\windows\system32\softpub.dll
regsvr32.exe /s c:\windows\system32\wintrust.dll
regsvr32.exe /s c:\windows\system32\dssenh.dll
regsvr32.exe /s c:\windows\system32\rsaenh.dll
regsvr32.exe /s c:\windows\system32\gpkcsp.dll
regsvr32.exe /s c:\windows\system32\sccbase.dll
regsvr32.exe /s c:\windows\system32\slbcsp.dll
regsvr32.exe /s c:\windows\system32\cryptdlg.dll
regsvr32.exe /s c:\windows\system32\oleaut32.dll
regsvr32.exe /s c:\windows\system32\ole32.dll
regsvr32.exe /s c:\windows\system32\shell32.dll
regsvr32.exe /s c:\windows\system32\initpki.dll
regsvr32.exe /s c:\windows\system32\wuapi.dll
regsvr32.exe /s c:\windows\system32\wuaueng.dll
regsvr32.exe /s c:\windows\system32\wuaueng1.dll
regsvr32.exe /s c:\windows\system32\wucltui.dll
regsvr32.exe /s c:\windows\system32\wups.dll
regsvr32.exe /s c:\windows\system32\wups2.dll
regsvr32.exe /s c:\windows\system32\wuweb.dll
regsvr32.exe /s c:\windows\system32\qmgr.dll
regsvr32.exe /s c:\windows\system32\qmgrprxy.dll
regsvr32.exe /s c:\windows\system32\wucltux.dll
regsvr32.exe /s c:\windows\system32\muweb.dll
regsvr32.exe /s c:\windows\system32\wuwebv.dll
echo Resetting Winsock…
netsh winsock reset
echo Resetting WinHTTP proxy…
netsh winhttp reset proxy
echo Resetting the services as automatic…
sc.exe config wuauserv start= auto
sc.exe config bits start= delayed-auto
sc.exe config cryptsvc start= auto
sc.exe config TrustedInstaller start= demand
sc.exe config DcomLaunch start= auto
echo Restarting services…
net start bits
net start wuauserv
net start appidsvc
net start cryptsvc
net start DcomLaunch
echo Telling Windows to detect updates…
wuauclt.exe /resetauthorization
wuauclt.exe /detectnow
wuauclt.exe /reportnow
PowerShell.exe (New-Object -ComObject Microsoft.Update.AutoUpdate).DetectNow()

Then finally once that runs through is the third batch file to set the computer back to a normal boot:

bcdedit /deletevalue {default} safeboot
shutdown -r -t 5

I put all three into a folder and placed it on the desktops of the troubled computers then ran each in sequence. First the computer rebooted in safe mode, then Windows Update gets reset along with deleting all temp files, then the computer reboots normally.

So far every computer this was run on has reported in. Not sure what causes this in the first place but at least we have a quick solution now.

Note: The script was written to be run in regular mode so its stopping services that are normally already stopped in safe mode but was just reused for this purpose on the machines where BITS kept getting stuck.

Batch Converting Visio VSD files to VSDX

I use Visio for my network and building layout drawings for simplicity. We have recently upgraded to Visio 2016 from 2007 and along with that there is a new file format, a .vsdx instead of a .vsd. Now I’m not sure of all the behind the scene changes but I do know that when you check a files properties you can reduce the file size by removing unused shapes, remove personal info, and save your old files as the new format. Between these three options the file size will drop 40 – 70% per file. Now when you have hundreds or even thousands of these you can gain a significant amount of file space back along with quicker overall loading and saving times. The problem is this looked to be a manual process.

So I did some searching and of course others have already tried to script this. I found two main articles, one on batch converting the vsd to vsdx and a second on removing the unused objects. I combined the two into a single script and added in the remove personal information option. So now you can do all three with a single script.

This is in VBA so to use this start out by opening a new blank Visio drawing. Hit ALT+F11 to get into Microsoft Visual Basic for Applications. Right click your drawing on the top left (by default Drawing1) and insert a module. Then copy and paste this in:

Sub ConvertToVsdx()
Dim strPath As String
Dim strFile As String
strPath = "C:\Temp2\"
strFile = Dir(strPath & "*.vsd")
Do While strFile <> ""
If Right(strFile, 3) = "vsd" Then
Application.Documents.Open strPath & strFile
Application.ActiveDocument.RemovePersonalInformation = True
' Loop through each master then check across pages to see if it is used
Index = Application.ActiveDocument.Masters.Count
While Index > 0
bMasterUsed = False
Set oMaster = Application.ActiveDocument.Masters.Item(Index)
For Each oPage In Application.ActiveDocument.Pages
For Each oShape In oPage.Shapes
If oMaster.Name = oShape.Name Then
bMasterUsed = True
End If
Next
Next
' if Not used delete it from the document stencil
If bMasterUsed = False Then
oMaster.Delete
End If
Index = Index - 1
Wend
Application.ActiveDocument.SaveAs strPath & strFile & "x"
Application.ActiveDocument.Close
End If
strFile = Dir
Loop
End Sub

Change the strPath to the directory you want to run this on and hit F5 to run. It will open each drawing with a vsd extension in that directory, set the remove personal information tag, remove the unused objects/stencils, then save it as a vsxd.

I haven’t adapted it to do subfolders yet as this is all I really needed but I’m sure there is a way to iterate through directories.