Skip to main content

Posts

Showing posts with the label Macro

Excel VBA get x days ago

A quick code snippet to get yesteday's date or a week before from the date the macro is executed.


Sub xDate()

Dim yesterdaysdate, aweekbeforefromtoday

aweekbeforefromtoday = Format(DateAdd("D", -Weekday(Date) - 1, Date), "dd-mmm-yy")


yesterdaysdate = Format(DateAdd("D", -1, Date), "dd-mmm-yy")


MsgBox yesterdaysdate
MsgBox aweekbeforefromtoday

End Sub


A variable can be set to easily change the desired number of days before the current date. 
Example:
xMinusDays = -2
yesterdaysdate = Format(DateAdd("D", xMinusDays, Date), "dd-mmm-yy")


xMinusDays is set to -2 will return 2 days before the current date

If  xMinusDays is set to -5 then it will return 5 days before from the current date

//===========================

VBA code below will return x days ago depends on the value set on the parameter.

To implement in VBA code:

=============================

Sub xDate()
Dim xMinusDays
xMinusDays = -365
Dim xdays

xdays = Format(DateAdd("D", xMin…

How to select objects behind text in word

Objects that are set as "Behind Text", cannot receive focus or cannot be selected when clicking the object.

If the  image, picture or  other objects cannot be selected. Then the object is set to display "behind text".

To select the picture, image or other objects. Follow steps below:

In Word ribbon, go to "Home" tab.

Select "editing", and click on "Select objects".

Then click on the object that is set as "behind text", then the object should be selected.

See screen shot below:







Hope it helps.. It will come handy, when you're in hurry.

Excel VBA adjust printing setup

Printing an Excel worksheet, can be adjusted manually.

In Excel 2010 and other versions of excel, printing can be adjusted by zoom in and zoom out.

In Excel 2010, to adjust page setup:

Click on "File".

Click on "Print".

Then the print preview is shown on the screen.

Under the "Custom Scaling" option, click on "Page Setup".

Click on "Adjust to" option and set the desired percentage.

See image below:



To do this via VBA or macro, it's quite straight forward.

See code below, just specify the "zoom" keyword and the value.

=========================
Sub VBA_PageSetup_Option()

With Worksheets("Sheet1")
 .PageSetup.Orientation = xlLandscape
 .PageSetup.Zoom = 280 '< - - - set the desired percentage
 '.PrintPreview 
 .PrintOut
End With

End Sub

 =========================

Code VBA above will zoom or adjust the whole worksheet.

If need to choose only a specific print area, then use the code below from Technet.

After sett…

Word VBA Get Content Control Value

Use SQL Query in Excel VBA

If you are an SQL junkie then using SQL in Excel VBA would be a good choice.

If just starting to embark on the SQL world, then it would be a fun also to use SQL statement in Excel VBA.

To start SQL queries in Excel VBA, it will need to reference to "Microsoft Activex Data Object 6.1 Library".

If Microsoft Activex Data Object 6.1 Library, is not found on the system.

Please refer to this Microsoft link:
https://support2.microsoft.com/kb/2640696?wa=wsignin1.0

To add reference using Excel VBA, press "Alt + F11" to open Excel VBA Project Window.

Click on "Tools" and click "References", then browse for Microsoft Activex Data Object 6.1 Library and click ok.

Or check out link below for the screen shot on how to add References in Excel VBA.

This link has screen shots on how to add references.
http://quickbytesstuff.blogspot.sg/2014/09/excel-vba-speak-method.html

Once the Activex Library is added, then it's time to code and have some fun.

There are tw…

Outlook VBA Get Contacts on Public Folders

How to get contacts on Outlook Public Folders using VBA?

Getting the contacts on the default folder is quite straight forward.

The GetDefaultFolder command on VBA can simply do the task.

Below is a screen shot of Outlook contact folder:

Above screen shot show the structure of Outlook contact folder, as a developer it's also good to know that the text or the string after the "-" sign is actually the default folder path.

And the string before the "-" sign is the sub folder name.

This is assuming that the Outlook settings has never been changed or customized by the user.

If ever the Contact folder has been changed by the user.
The settings can be verified also by right clicking on the contact folder and selecting properties.

See screen shot below:


Outlook Contacts in Public Folders has also the same structure with the default folder contacts but accessing the data or the items via VBA is not the same.

Public Contact Folder screen shot:


To make life easier, just c…

How to use batch file to register DLLs or to move or copy files

Batch file scripts below will register DLLs, copy or moved files.
Be careful in moving files as it would overwrite if there is an existing file on the destination folder. This batch file script below would register DLL files in a specified folder.
REM Start Copy Here REM Register DLL files @echo off set cnt=0 for %%v in (C:\MyCustomApp\*.dll) do (    regsvr32 /s "%%v"    call set /a cnt=%%cnt%%+1    ) echo Total dlls found and registered = %cnt% REM End of Batch File

Using same batch file it can be tweak to copy any specified files to a particular folder.
If you have a folder with PDF, Excel Files or other documents.
And you just want to copy files or move files to another folder, batch scripts below would help.
This simple batch file below would copy files to a specified folder.
REM Start Copy Here REM Copy PDF Files to another folder @echo off set cnt=0 for %%v in (d:\MixFilesFolder\*.pdf) do (    copy "%%v" "d:\AllPDF"    call set /a cnt=%%cnt%%+1    ) echo Total PDFs …

Unable to install Teamviewer on Windows 7 and Windows 8

Installing Teamviewer on Windows 7 and Windows 8, has an error message of “rollback framework could not be initialized, installation aborted”.
To resolve this issue login to an administrator user account.
Go to an elevated command prompt and enable the hidden admin account.

Click on the link below, to check on how to open an elevated command prompt using Windows 7.

http://quickbytesstuff.blogspot.sg/2014/10/open-elevated-command-prompt.html


To open an elevated command prompt in Windows 10 check out link below:

http://quickbytesstuff.blogspot.sg/2015/08/quickly-access-command-prompt-or.html


Type:  net user administrator /active:yes
If the command prompt is not elevated you will receive this error:
System error 5 has occurred. Access is denied.
However if the command is successful, it will show:
The command completed successfully.