Force close Excel Workbook Opened by another user

You can open the computer management and then in
System Tools >Shared Folders > Open Files

you will find all open file...here you can right click the files and close them

 


VBA Save All Outlook Attachments

VBA to save all outlook email attachments.  Change Set myFolder = myNamespace.Folders.Item("Dylan.Gregory@vba-market.com").Folders("Inbox") to your outlook email folder path.
.

 

Dim myAttachments As Outlook.Attachments
Set myOlApp = CreateObject("Outlook.Application")
Set myNamespace = myOlApp.GetNamespace("MAPI")
Set myFolder = myNamespace.Folders.Item("Dylan.Gregory@vba-market.com").Folders("Inbox")
'myFolder.Display

itemsCount = myFolder.Items.Count
i = 0
For i = 1 To itemsCount
Set myItem = myFolder.Items(i)
myItem.Display

Set myAttachments = myItem.Attachments
If myItem.Attachments.Count > 0 Then
ipath = "Z:\Dylan\attachments\"
On Error Resume Next
myAttachments.Item(i).SaveAsFile ipath & myAttachments.Item(i).DisplayName
' MsgBox myAttachments.Item(i).DisplayName & " has been saved to " & ipath
On Error GoTo 0

End If

Excel Date Time Format (DateTimeFormat)

Excel DateTimeFormats

CharacterDescriptionFormat ArgumentFormatted Display
=NOW()Shows current date and time=NOW()10/21/2018 8:01
dDay is displayed as a number, as one digit or as two digit, without a leading zero. (1-31)d8
ddDay is displayed as a number, as two digit, with a leading zero where applicable. (01-31)dd08
dddDay is abbreviated to three letters, viz. Sunday is displayed as Sun. (Sun-Sat)dddMon
ddddDay is displayed in its full format, viz. Sunday is displayed as Sunday. (Sunday-Saturday)ddddddMonday
wDay of the week is displayed as a number (1-7 for Sunday-Saturday).w, mmm d, yyyy2, Jul 8, 2013
ww Week of the year is displayed as a number. (1 to 54)ww28
mMonth is displayed as a number, as one digit or as two digit, without a leading zero - (1-12).  To use m as minute(s), it should appear immediately after the h or hh code, such as "h:m".m7
mmMonth is displayed as a number, as two digit, with a leading zero where applicable - (01-12). To use mm as minute(s), it should appear immediately after the h or hh code, such as "h:mm".mm07
mmmMonth name is abbreviated to three letters, viz. January is displayed as Jan. (Jan-Dec)mmmJul
mmmmMonth is displayed in its full name, viz. January is displayed as January. (January-December)mmmmJuly
qQuarter of the year is displayed as a number. (1 to 4)q3
yDay of the year is displayed as a number. (1 to 366)y189
yyYear is displayed as a number in two digits, viz. last 2 digits of the year are displayed. (00-99)yy13
yyyyYear is displayed as a number in four digits, viz. all digits of the year are displayed. (1900-9999)yyyy2013
Time separator. This separates hours, minutes & seconds while formatting time values. Changes m and mm to represent minutes instead of months.h:n:ss AMPM6:01:26 AM
hHour is displayed as a number, as one digit or as two digit, without leading zeros. (0-23)h6
HhHour is displayed as a number, as two digit, with a leading zero where applicable. (00-23)Hh06
mMinute is displayed as a number, as one digit or as two digit, without leading zeros -  (0 to 59). You can also use m code as minute (m is used for displaying month), for which it should appear immediately after the h or hh code, such as "h:m".m:ss1:26
mmMinute is displayed as a number, as two digit, with a leading zero where applicable -  (00 to 59). You can also use m code as minute (m is used for displaying month), for which it should appear immediately after the h or hh code, such as "h:m".mm:s1:26
SSecond is displayed as a number, as one digit or as two digit, without leading zeros. (0 - 59)h:mm:S6:13:6
SsSecond is displayed as a number, as two digit, with a leading zero where applicable.  (00 - 59)h:mm:Ss6:13:06
000Millisecondh:mm:ss.0008:27:11.2711
AM/PM, am/pmIf these codes are included in the format, the hour is displayed using a 12-hour clock, else the hour is based on the 24-hour format. Display will include AM, am, A or a for a time before noon, and PM, pm, P or p for a time 'from' and 'after' noon till 11:59 PM. While using AMPM, the case can be UPPER or lower, matching the string as defined by your system settings. AM/PM is the Default format.Hh:n:ss AM/PMAM
General Datestr = Format(SerialNo, "General Date")General Date7/31/2014 20:52
Long Datestr = Format(MyDate, "Long Date")Long DateThursday, July 31, 2014
Medium Datestr = Format(MyDate, "Medium Date")Medium Date31-Jul-14
Short Datestr = Format(MyDate, "Short Date")Short Date7/31/2014
Long Timestr = Format(MyTime, "Long Time")Long Time7:16:32 PM
Medium Timestr = Format(MyTime, "Medium Time")Medium Time7:16 PM
Short Timestr = Format(MyTime, "Short Time")Short Time19:16

Most commonly used Excel Date Time Formats and short codes for each type.

Excel DateTimeFormat Common Types

FormatResult
d-mmm-yy1-Jul-14
dd-mmm-yy01-Jul-14
dd-mmm-yyyy01-Jul-2014
dddd, dd mmmm, yyySunday, 21 October, 2018
dd-mm-yy21-10-18
dd-mm-yyyy21-10-2018
dd mmmm, yyyy21 October, 2018
mm/dd/yy10/02/18
mm/dd/yyyy10/02/2018
ddd, mmmm dd, yyyySun, October 21, 2018
m/d/yyyy9/2/2018
m/dd/yyyy9/02/2018
mm/d/yyyy09/2/2018
mm/dd/yyyy09/02/2018
dd-mmm21-Oct
dd-mmm-yy21-Oct-18
mmmm, yyyyOctober, 2018
yyyymmdd20181021

 


VBA Word Insert Image Header

VBA to insert a header image into Word document.  Change filepath to your image.

*Note if  you get error:  The remote server machine does not exist or is unavailable.  That just means Word connection could not be established.  Exit out of Word and try again.  Should work if Word is closed.

Enable Microsoft Word 16.0 Object Library
Sub insertHeaderImage()
Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add

With wrdDoc

' Set header of doc


Dim BoldRange As Range
Dim HdrRange As Range


Set docActive = Word.ActiveDocument
ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.InlineShapes.AddPicture "C:\Users\dgreg\Desktop\header.PNG"

End With

End Sub


VBA Word Create Table

VBA to create word document with a table.   Define number of rows and number of columns in the intNoOfRows and intNoOfColumns variables.

Enable Microsoft Word 16.0 Object Library

Dim wrdDoc As Word.Document
Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
Set wrdDoc = wrdApp.Documents.Add
With wrdDoc
   Dim intNoOfRow, intNoOfColumns As Long
   Set objRange = wrdDoc.Content
   objRange.Collapse Direction:=wdCollapseEnd
   intNoOfRows = 2
   intNoOfColumns = 2
   wrdDoc.Tables.Add objRange, intNoOfRows, intNoOfColumns

   Set objTable = wrdDoc.Tables(1)
   objTable.Borders.Enable = True
   objTable.Cell(1, 1).Range.Text = "Change Related To"
   objTable.Cell(1, 1).Range.Bold = True
   objTable.Cell(1, 2).Range.Text = "Change Description"
   objTable.Cell(1, 2).Range.Bold = True
   objTable.Cell(2, 1).Range.Text = "xxx"
   objTable.Cell(2, 2).Range.Text = "yyy"
   objTable.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
End With

VBA parse JSON to extract JSON response value

VBA to parse JSON.  Extract values out of JSON response from API Call.  Uses VBA-JSON.

VBA-JSON bas file (53 downloads)
  1. Download the zip file and extract JsonConverter.bas.
  2. Then open your VBA editor window and go file -> import file -> select JsonConverter.bas.
  3. The JSON parsing code will be added to your modules.

Example1:

Sub getData()
Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
MyRequest.Open "GET", "https://jira.atlassian.com/rest/api/2/issue/JRA-9.json"
MyRequest.send
MsgBox MyRequest.responseText

Dim json As Object
Set json = JsonConverter.ParseJson(MyRequest.responseText)
MsgBox json("key")
MsgBox json("fields")("fixVersions")(1)("id")
End Sub

Example2:

Add following libraries


Sub xmlHttp()
Const URl As String = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=ICICIBANK"
Dim xmlHttp As Object
Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
xmlHttp.Open "GET", URl & "&rnd=" & WorksheetFunction.RandBetween(1, 99), False
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send

Dim html As MSHTML.HTMLDocument
Set html = New MSHTML.HTMLDocument
html.body.innerHTML = xmlHttp.responseText

Dim divData As Object
Set divData = html.getElementById("responseDiv")

Dim strDiv As String, startVal As Long, endVal As Long
strDiv = divData.innerHTML
startVal = InStr(1, strDiv, "data", vbTextCompare)
endVal = InStr(startVal, strDiv, "]", vbTextCompare)
strDiv = "{" & Mid(strDiv, startVal - 1, (endVal - startVal) + 2) & "}"
strDiv = Replace(strDiv, "{""data"":[", "")
strDiv = Replace(strDiv, "]}", "")
MsgBox strDiv
Cells(1, 1).Value = strDiv
Set json = JsonConverter.ParseJson(strDiv)
MsgBox json("symbol")

End Sub

VBA Get JSON Response Text

VBA to get JSON response text from an api call. Vba will extract the data as a JSON string which can then be parsed to extract the node values.  This code only creates the JSON string but does not parse the string.

 

The following reference libraries need to be enabled.  Forms 2.0 library can be added by adding a user form in the vba editor.

 

Const URl As String = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=ICICIBANK"
Sub xmlHttp()

Dim xmlHttp As Object
Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
xmlHttp.Open "GET", URl & "&rnd=" & WorksheetFunction.RandBetween(1, 99), False
xmlHttp.setRequestHeader "Content-Type", "text/xml"
xmlHttp.send

Dim html As MSHTML.HTMLDocument
Set html = New MSHTML.HTMLDocument
html.body.innerHTML = xmlHttp.responseText

Dim divData As Object
Set divData = html.getElementById("responseDiv")

Dim strDiv As String, startVal As Long, endVal As Long
strDiv = divData.innerHTML
startVal = InStr(1, strDiv, "data", vbTextCompare)
endVal = InStr(startVal, strDiv, "]", vbTextCompare)
strDiv = "{" & Mid(strDiv, startVal - 1, (endVal - startVal) + 2) & "}"
MsgBox strDiv

End Sub

VBA ODBC Connect to SQL Database

VBA to pull SQL database using ODBC connection.  First you have to setup the ODBC connection in ODBC Data Source Administrator.  Then the VBA references the ODBC name for the connection.  Make sure to enable Microsoft ActiveX Data Object Library otherwise you'll get the error "Compile error:  User-defined type not defined.".

Dim cnDB As New ADODB.Connection

'Enable Microsoft ActiveX Data Objects v.0 Library
Dim rsRecords As New ADODB.Recordset

'Open the ODBC Connection using this statement
cnDB.Open "DataWarehouse"
rsRecords.Open "SELECT TOP 10 * FROM account.data_account", cnDB

'Print the table to Excel
Range("A1").CopyFromRecordset rsRecords


'Close everything and set the references to nothing
rsRecords.Close
Set rsRecords = Nothing
cnDB.Close
Set cnDB = Nothing

VBA Convert String Date to Date Value

VBA formula to convert a Text date to Date format.  Convert string date to date format.
CDate part of the formula converts the string to a date.
Format part of the formula lets you decide what format the date should be in.

datestr = Format(CDate(Cells(1, 1).Value), "dd/mm/yyyy")
datestr2 = Format(CDate(Range("A1").Value), "dd/mm/yyyy")
datestr3 = Format(CDate("2018-01-10"), "dd/mm/yyyy")

DateTime formats

Character Description Format Argument Expression Argument Formatted Display
/ (forward slash) Date separator. Separates the day, month & year while formatting date values. mm/dd/yyyy 41463 07/08/2013
c Date is displayed as ddddd and time is displayed as ttttt, in that order. Only date is displayed if date serial number does not have a fractional part and only time information is displayed if no integer portion. c 41463.251 7/8/2013 6:01:26 AM
  c 0.251 6:01:26 AM
  c 41463 7/8/2013
d Day is displayed as a number, as one digit or as two digit, without a leading zero. (1-31) mm/d/yyyy 41463 07/8/2013
dd Day is displayed as a number, as two digit, with a leading zero where applicable. (01-31) mm/dd/yyyy 41463 07/08/2013
ddd Day is abbreviated to three letters, viz. Sunday is displayed as Sun. (Sun-Sat) ddd, mmm d, yyyy 41463 Mon, Jul 8, 2013
dddd Day is displayed in its full format, viz. Sunday is displayed as Sunday. (Sunday-Saturday) dddd, mmm d, yyyy 41463 Monday, Jul 8, 2013
ddddd Display a date serial number as a complete date (including day, month, and year) formatted according to the short date setting recognized by your system. The default short date format is m/d/yy. ddddd 41463 7/8/2013 (Short Date Format in the system showing the display is: dddd, m/d/yyyy)
dddddd Display a date serial number as a complete date (including day, month, and year) formatted according to the long date setting recognized by your system. The default long date format is mmmm dd, yyyy. dddddd 41463 Monday, July 8, 2013  (Long Date Format in the system showing the display is: dddd, MMMM d, yyyy)
aaaa Displays the full, localized name of the day in its full format (same as dddd, except that its localized version).
w Day of the week is displayed as a number (1-7 for Sunday-Saturday). w, mmm d, yyyy  41463 2, Jul 8, 2013
ww  Week of the year is displayed as a number. (1 to 54) ww #7/8/2013# 28
m Month is displayed as a number, as one digit or as two digit, without a leading zero - (1-12).  To use m as minute(s), it should appear immediately after the h or hh code, such as "h:m". m/d/yyyy 41463 7/8/2013
mm Month is displayed as a number, as two digit, with a leading zero where applicable - (01-12). To use mm as minute(s), it should appear immediately after the h or hh code, such as "h:mm". mm/d/yyyy 41463 07/8/2013
mmm Month name is abbreviated to three letters, viz. January is displayed as Jan. (Jan-Dec) mmm d, yyyy 41463 Jul 8, 2013
mmmm Month is displayed in its full name, viz. January is displayed as January. (January-December) mmmm d, yyyy 41463 July 8, 2013
oooo Displays the full localized name of the month (same as mmmm, except that its localized version).
q Quarter of the year is displayed as a number. (1 to 4) q #7/8/2013# 3
y Day of the year is displayed as a number. (1 to 366) y #7/8/2013# 189
yy Year is displayed as a number in two digits, viz. last 2 digits of the year are displayed. (00-99) m/d/yy 41463 7/8/13
yyyy Year is displayed as a number in four digits, viz. all digits of the year are displayed. (1900-9999) m/d/yyyy 41463 7/8/2013
 
Time separator. This separates hours, minutes & seconds while formatting time values. h:n:ss AMPM 0.251 6:1:26 AM
h Hour is displayed as a number, as one digit or as two digit, without leading zeros. (0-23) h:nn:ss AMPM 0.251 6:01:26 AM
Hh Hour is displayed as a number, as two digit, with a leading zero where applicable. (00-23) Hh:n:ss AMPM 0.251 06:1:26 AM
N Minute is displayed as a number, as one digit or as two digit, without leading zeros -  (0 to 59). You can also use m code as minute (m is used for displaying month), for which it should appear immediately after the h or hh code, such as "h:m". n:ss 0.251 1:26
Nn Minute is displayed as a number, as two digit, with a leading zero where applicable -  (00 to 59). You can also use m code as minute (m is used for displaying month), for which it should appear immediately after the h or hh code, such as "h:m". Nn:s 0.251 01:26
S Second is displayed as a number, as one digit or as two digit, without leading zeros. (0 - 59) h:n:S 0.2591 6:13:6
Ss Second is displayed as a number, as two digit, with a leading zero where applicable.  (00 - 59) h:n:Ss 0.2591 6:13:06
t t t t t   Complete time is displayed (including hour, minute, and second) wherein the time separator, as defined by the time format recognized by your system, is used. If the leading zero option is selected and the time is before 10:00 AM / PM, a leading zero will be displayed. h:mm:ss is the default format. ttttt 0.25631 6:09:05 AM
AM/PM, am/pm, A/P, a/p or AMPM If these codes are included in the format, the hour is displayed using a 12-hour clock, else the hour is based on the 24-hour format. Display will include AM, am, A or a for a time before noon, and PM, pm, P or p for a time 'from' and 'after' noon till 11:59 PM. While using AMPM, the case can be UPPER or lower, matching the string as defined by your system settings. AM/PM is the Default format. Hh:n:ss A/P 0.251 06:1:26 A

Date time format keywords

'returns "7/31/2014 8:52:48 PM"
str = Format(SerialNo, "General Date")
MsgBox str

'returns "Thursday, July 31, 2014"
str = Format(MyDate, "Long Date")
MsgBox str

'returns "31-July-14"
str = Format(MyDate, "Medium Date")
MsgBox str

'returns "7/31/2014"
str = Format(MyDate, "Short Date")
MsgBox str

'returns "7:16:32 PM"
str = Format(MyTime, "Long Time")
MsgBox str

'returns "07:16 PM"
str = Format(MyTime, "Medium Time")
MsgBox str

'returns "19:16"
str = Format(MyTime, "Short Time")
MsgBox str


VBA Check If File Exists

VBA to check if a file exists within a folder.  Update strpath with your filepath.  And update filecheck with your filename.  VBA will then check if that file exists within the designated folder.

 

Dim FSO
Dim sFile As String

strpath = "Z:\Downloads\JSON"
If Right(strpath, 1) <> "\" Then strpath = strpath + "\"
ChDrive "Z:\"
ChDir strpath
strextend = Dir("*.*")
filecheck = "Filename.json"
Do While strextend <> ""

sFile = strpath & strextend
checkFile = strpath & filecheck
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(checkFile) Then
MsgBox "FileExists=TRUE"
Else
MsgBox "FileExists=FALSE"
End If

strextend = Dir
Loop