This is a collection of other useful script excerpts. VBScript examples have now been removed as this is planned for deprecation in future versions of Windows.
Creating a PowerPoint presentation from a large amount of photos can be very time consuming. Office 2007 and later include a Photo Album feature to assist with importing photos in bulk but this still requires some manual selection of images and can be awkward to use.
The attached macro enabled powerpoint presentation is a demonstration of how VBA can be used to automate this process. This sample includes functions to import all JPG,PNG and GIF images in the folder of the presentation and additional functions and forms to display a progress bar indicating progress for large volumes of images.
The code included in this presentation can be run as a macro or run as part of the presentation. Details are included on the slideshow. This presentation will work in Office 2007 and later.
27/04/2015 - V1.0.0.0 Release
Microsoft Outlook includes an import/export feature that can be used to create calendar items from a spreadsheet. This method however only works on your primary mailbox.
If you have direct access to your shared mailboxes or exchange server there are other solutions but as a user this makes it difficult to automatically create calendar items in a shared mailbox. Looking for a solution I discovered an easy way to create a VBA macro that could be used to select and create calendar items in any of my mailboxes. Simply populate this spreadsheet and run the import and you can watch as calendar items are created and meeting requests automatically sent.
19/04/2015 - V1.0.0.0 Release
A common challenge for database administrators is identifying queries from an application or user that are impacting the performance of a SQL server. These are usually long running queries that can either slow down performance of the SQL server or cause the TEMPDB (the temporary database used by SQL for processing) to grow in size dramatically. Either of these is likely to have an impact on the user experience. These scripts will help you identify these queries.
These scripts have been tested on SQL 2008 R2 and SQL 2012.
The first step is to identify what queries are running and for how long they have been running. The following SQL statement allows you to identify those processes and excludes system functions. The SPID for each process is shown to assist in identifying the process so it can be terminated.
-- Current Long Running Database Queries
SELECT
[spid] = session_Id
,ecid
,[blockedBy] = blocking_session_id
,[database] = DB_NAME(sp.dbid)
,[user] = nt_username
,[status] = er.STATUS
,[wait] = wait_type
,[current stmt] =
SUBSTRING(
qt.TEXT,
er.statement_start_offset / 2,
(CASE
WHEN er.statement_end_offset = - 1 THEN DATALENGTH(qt.TEXT)
ELSE er.statement_end_offset
END - er.statement_start_offset) / 2)
,[current batch] = qt.TEXT
,reads
,logical_reads
,cpu
,[time elapsed (min)] = DATEDIFF(mi, start_time, getdate())
,program = program_name
,hostname
,start_time
,qt.objectid
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
ORDER BY 1,2
If these queries have been running for a long time it is likely the TEMPDB database contains a lot of data or running threads related to these processes. This could be consuming disk space rapidly. The following SQL statements allow you to identify the processes that are using TEMPDB, the current configuration of this database and any impact on drive space. This SQL statement comprises 5 queries to achieve this. It calculates (in order) the size of the TEMPDB, freespace inside the files, free space on the drives, the size and growth of the TEMPDB files, what is currently using the TEMPDB (by SPID) and the database snapshots running.
-- Size of TEMPDB
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files
-- Free Space in TEMPDB
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;
-- Drive Space on SQL Server
EXEC master..xp_fixeddrives
-- Size and Growth Type of TEMPDB
SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END AutogrowthStatus,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0
AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
GO
-- Current Active SPIDs using TEMPDB
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage WHERE internal_objects_alloc_page_count > 0 OR internal_objects_dealloc_page_count > 0
GROUP BY session_id;
-- Current Running Database Snapshots
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
With this information you can now retrieve the queries that are causing either a performance impact or are using drive space on the SQL server. If you identify a query that is causing an impact you can investigate that query to identify why it is so large or you could kill a query by running the simple SQL statement of "Kill <SPID>".
If you have a large number of word documents in a folder that you would like to convert in bulk to PDF files then the fastest way to achieve this is using PowerShell.
To do this create a powershell file using the following powershell code in notepad.
$Word=New-Object -ComObject Word.Application
$Files=Get-ChildItem ".\*.doc"
ForEach ($File In $Files) {
$Document=$Word.Documents.Open($File.FullName)
$Name=($Document.FullName).Replace("doc", "pdf")
$Document.SaveAs([ref] $Name, [ref] 17)
$Document.Close()
}
$Files=Get-ChildItem ".\*.docx"
ForEach ($File In $Files) {
$Document=$Word.Documents.Open($File.FullName)
$Name=($Document.FullName).Replace("docx", "pdf")
$Document.SaveAs([ref] $Name, [ref] 17)
$Document.Close()
}
Place the above powershell file in the folder of Word documents you want to convert and run the powershell commands to convert the documents.
To simplify running this process you can created a command file in notepad that you can also place in the same folder and run to kickoff the processing.
powershell.exe -noprofile -executionpolicy bypass -file .\Convert-DocumentsToPDF.ps1
This should save you considerable time if you have a large number of documents to convert and requires no extra software.
Christian specialises in translating complex business requirements into technical solutions. With a degree in Communications and over 20 years experience in IT solutions, implementation, training, troubleshooting and software development he has developed a skill set designed to help any business maximise the potential of their IT spend.
With expertise in Consumer Goods retail execution, life sciences relationship management and data driven cross platform technologies Christian can help enhance sales revenue and the return on investment in field selling resources.
Christian Dunn