Play Audio File using Excel VBA

Play Audio File using Excel VBA / Excel VBA Play Wav File

Here is a small snippet used by Pradeep Meesala to play a wav file. He has used this for an elegant splash screen, which was a nice combination of music and display

The code uses PlaySound API Function

Private Declare Function PlaySound Lib "winmm.dll" _

Alias "PlaySoundA" (ByVal lpszName As String, _

ByVal hModule As Long, ByVal dwFlags As Long) As Long

Sub Play_Wav_File()

Dim sWAVFile As String

On Error GoTo Err_Play

sWAVFile = "C:\Temp\Windows XP Logon Sound.wav"

PlaySound(sWAVFile, &O0, 0)

Err_Play:

If Err <> 0 Then

Err.Clear()

End If

End Sub

VBA Send File to Recycle Bin

Delete Files using VBA

There are many methods to delete the file:- simple VBA Kill method, using FileSystemObject etc. Here is one snippet my friend Devakottai PaneerSelvam used for deleting files (if I can say it, temporarily). The advantage here is that the deleted file is available in Recycle Bin for the user to restore if needed.

Private Type SHFILEOPSTRUCT

hwnd As Long

wFunc As Long

sFrom As String

sTo As String

fFlags As Integer

fAnyOperationsAborted As Boolean

hNameMappings As Long

lpszProgressTitle As String

End Type

Private Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" _

(ByRef lpFileOp As SHFILEOPSTRUCT) As Long

Private Const FO_DELETE = &H3

Private Const FOF_SILENT = &H4

Private Const FOF_NOCONFIRMATION = &H10

Private Const FOF_ALLOWUNDO = &H40

Sub DeleteFileUsingAPI()

Dim oFilAPI As SHFILEOPSTRUCT

Dim lReturn As Long

Dim sFile As String ' File that needs to be deleted

sFile = "C:\ Temp\VBADUD\Hints and Tips.htm"

With oFilAPI

.wFunc = FO_DELETE

.sFrom = sFile

.sTo = vbNullChar

.fFlags = FOF_SILENT + FOF_NOCONFIRMATION + FOF_ALLOWUNDO

End With

' Use WinAPI User Defined Function

lReturn = SHFileOperation(oFilAPI)

Err_Delete:

MsgBox(Err.Number & " - " & Err.Description)

Err.Clear()

Resume Next

End Sub

Delete Files using VBA, Temporary Deletion using VBA, VBA Code to send files to Recycle Bin

See also:

Move and Overwrite Files in C# (.NET)

Delete Files using C# / Delete Files using .NET

Clean Temp (.tmp) and Word Backup files (.wbk) using Word VBA

Primitive File Handling Functions

Show All Processes using VBA

Get All Processes using Win API Functions

'Declarations
Const TH32CS_SNAPHEAPLIST = &H1
Const TH32CS_SNAPPROCESS = &H2
Const TH32CS_SNAPTHREAD = &H4
Const TH32CS_SNAPMODULE = &H8
Const TH32CS_SNAPALL = (TH32CS_SNAPHEAPLIST Or TH32CS_SNAPPROCESS Or TH32CS_SNAPTHREAD Or TH32CS_SNAPMODULE)
Const TH32CS_INHERIT = &H80000000
Const MAX_PATH As Integer = 260
Private Type PROCESSENTRY32
dwSize As Long
cntUsage As Long
th32ProcessID As Long
th32DefaultHeapID As Long
th32ModuleID As Long
cntThreads As Long
th32ParentProcessID As Long
pcPriClassBase As Long
dwFlags As Long
szExeFile As String * MAX_PATH
End Type
Private Declare Function CreateToolhelp32Snapshot Lib "kernel32" (ByVal lFlags As Long, ByVal lProcessID As Long) As Long
Private Declare Sub CloseHandle Lib "kernel32" (ByVal hPass As Long)

' API Functions to get the processes
Private Declare Function Process32First Lib "kernel32" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long
Private Declare Function Process32Next Lib "kernel32" (ByVal hSnapShot As Long, uProcess As PROCESSENTRY32) As Long


Sub Load_Process_To_ListBox()


Dim hSnapShot As Long '* Handle
Dim uProcess As PROCESSENTRY32 '* Process
Dim lRet '* Return Val

On Error Resume Next

'Takes a snapshot of the running processes and the heaps, modules, and threads used by the processes
hSnapShot = CreateToolhelp32Snapshot(TH32CS_SNAPALL, 0&)

uProcess.dwSize = Len(uProcess)

'Retrieve information about the first process encountered in our system snapshot

lRet = Process32First(hSnapShot, uProcess)

Do While lRet
lRet = Process32Next(hSnapShot, uProcess)

' Trim the unwanted characters at the end of process
lstProcess.AddItem Left$(uProcess.szExeFile, IIf(InStr(1, uProcess.szExeFile, Chr$(0)) > 0, InStr(1, uProcess.szExeFile, Chr$(0)) - 1, 0))
Loop


CloseHandle hSnapShot

End Sub



Private Sub UserForm_Initialize()

' Call the Function

Load_Process_To_ListBox

End Sub

Companies House

Duport provide company formation, company credit reports and director reports.

Run a VB6.0 Executable from Excel/Word

Run an Executable from Excel VBA / Word VBA

If you need to use some grid for showing data / use the feautres in Visual Basic 6.0 that arenot available in VBA, you can create the application in VB6.0 or anyother program and show the User Interface in VBA code


Sub Run_VB6App_FromWord()

--- Some VBA Code here

sCmd = "C:\Program Files\MyFile.exe"
vntResult = OpenProcess(PROCESS_QUERY_INFORMATION, False, Shell(sCmd, 1))
GetExitCodeProcess vntResult, lngExitCode

' -----------------------------------------------------------
' Coded by Shasur for http://vbadud.blogspot.com
' -----------------------------------------------------------

Do
GetExitCodeProcess vntResult, lngExitCode
DoEvents
Loop While lngExitCode = STILL_ACTIVE

--- some more VBA Code

End Sub


The above program will show the MyFile executable till the user clicks OK/Cancel. Once the application is closed the control will return to the calling VBA program

This used WinAPI Functions

Public Declare Function GetExitCodeProcess Lib "kernel32" (ByVal hProcess As Long, lpExitCode As Long) As Long
Public Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

Now it depends on how you use the external application. The most often used scenario will be to store the output from the called application (External App) to Registry or Database

Visual Basic Function to Get Temporary Folder

Windows API Function to Get Temporary Folder

Private Declare Function GetTempPath Lib "kernel32" Alias _
"GetTempPathA" (ByVal nBufferLength As Long, ByVal _
lpBuffer As String) As Long

Const MAX_PATH = 260

' This function uses Windows API GetTempPath to get the temporary folder

Sub Get_Temporary_Folder()

sTempFolder = GetTmpPath

End Sub

' Keywords: Get Temporary Folder, Temporary Folder Visual Basic Code, VB Function Get Temp Folder, VBA Temporary Folder, VB6 Temporary Folder, GetTempPath, Windows API Functions


Private Function GetTmpPath()

Dim sFolder As String ' Name of the folder
Dim lRet As Long ' Return Value

sFolder = String(MAX_PATH, 0)
lRet = GetTempPath(MAX_PATH, sFolder)

If lRet <> 0 Then
GetTmpPath = Left(sFolder, InStr(sFolder, _
Chr(0)) - 1)
Else
GetTmpPath = vbNullString
End If

End Function

This function can be used to identify the Temporary folders for Windows XP kind of OS, where each login will have its own temp folder