I. EXCEL

1) Changing format: text into value

2) Date can be convert into text

3) Saving activeWorkbook as "csv" with date stamp

4) Swifting workbooks

II. ACCESS

1) Transfering Table into excel file (it is NOT limited to 65k rows)

III. VBA

1) Deleting files and folders

2)Order to wait

3) Declaration responsible for opening new window in maximum size

3a) Opening Internet Explorer Web Browser and navigation to http site (using declaration)

4) Opening folder

 

I. EXCEL

1) Changing format: text into value 

Selection.NumberFormat = "0.00"

For Each xCell In Selection

xCell.Value = CDec(xCell.Value)

Next xCell

 

2) Date can be convert into text

Selection.NumberFormat = "@"

 

3) Saving activeWorkbook as "csv" with date stamp

ActiveWorkbook.SaveAs FileName:= _

"C:\MyPath\MyFileName_" & Format(Date, "yyyymmdd") & ".csv", FileFormat:= _

xlCSV, CreateBackup:=False

 

4) Swifting workbooks

Application.ActiveSheet

or

ActiveWorkbook.ActiveSheet

 

II. ACCESS

1) Transfering Table into excel file (it is NOT limited to 65k rows)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TableName", "C:\MyPath\MyFileName.xlsx", True

 

III. VBA

1) Deleting files and folders

 aFile = "C:\Programy\MyHtmlFile.htm" ' kills single file

If Len(Dir$(aFile)) > 0 Then

Kill aFile

Kill "C:\Programy\MyFiles\*.*" ' kills every file in tjhe folder

RmDir "C:\Programy\MyFolder\" ' kills folder (files must be deleted at first)

End If

 

2) Order to wait

The first method

Dim currenttime As Date     

currenttime = Now

Do Until currenttime + TimeValue("00:00:05") <= Now

Loop

 

The second method

If Application.Wait(Now + TimeValue("0:00:10")) Then

MsgBox "Your session has expired"

End If

 

3) Declaration responsible for opening new window in maximum size

Declare Function apiShowWindow Lib "user32" Alias "ShowWindow"   (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Global Const SW_MAXIMIZE = 3

Global Const SW_SHOWMINIMIZED = 2

Global Const SW_SHOWNORMAL = 1

 

3a) Opening Internet Explorer Web Browser and navigation to http site (using declaration)

Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = True

apiShowWindow IE.hwnd, SW_MAXIMIZE

On Error Resume Next

IE.navigate "http://Ilearn.com.pl"

Set IE = Nothing

 

4) Opening folder

Dim str_folder As String

str_folder = "C:\folder\Yourfolder\"  ' folder to open

Call Shell("explorer.exe " & str_folder, vbNormalFocus)

 

5) With Selection Replace

With Selection

.Replace What:="before", Replacement:="after"

.Replace What:="Mbefore", Replacement:="after"

End With

 

6) Time elapsed

start1 = Time()

stop1 = Time()

TimeElapsed = stop1 - start1

MsgBox Format(sngElapsed, "hh:mm:ss")

 

6) Application.ScreenUpdating

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False