Erlandsen Data ConsultingSometimes you don’t need to ask the user to select a list of files that you want to do something with. If you already know the source folder and types of files you want to retrieve you can use the function below. This function is also a simple replacement for the Application.FileSearch that is not [...]
The built-in method Workbooks.Add creates a new workbook with a predetermined number of worksheets, usually 3, but this can be another number if the user has changed the setting for how many worksheets a new workbook should contain. I really hate to have empty, unused worksheets in my workbooks, and I am fed up with [...]
In Excel we find the really nice functionality Application.GetOpenFilename and Application.GetSaveAsFilename that makes it really easy to ask the user for one or more filenames when opening or saving files. In other Office-applications this is sometimes not equally easy to achieve. The good news is that you can borrow this nice functionality from Excel in [...]
This is an update or addition to the old article about the same topic. With the function below it is possible to let the user select a folder. The function returns the complete path to the selected folder and you can use that information in your macro to e.g. save a document or read input [...]
How to get the active station and folder name: MsgBox “Active station and folder name: ” & CurDir How to change the active station: ChDrive “F” ‘ changes to the F-station How to change the active folder: ChDir “F:\My Documents\Private” How to determine if a file exists in a folder: If Len(Dir(“F:\My Documents\My Workbook.xls”)) > [...]
The function below can determine if a folder exists, it can also create any missing folders ine the folder path: Function FolderExists(strInputFolder As String, blnCreate As Boolean) As Boolean ‘ returns true or false if the folder exists, can create any missing folders ‘ example: If Not FolderExists("C:\FolderName\SubFolder", False) Then Exit Sub ‘ example: If [...]
Microsoft Scripting Runtime is included in these products: Windows98, Windows2000, IE5, and Office2000. The macro examples below assumes that your VBA project has added a reference to the Microsoft Scripting Runtime library. You can do this from within the VBE by selecting the menu Tools, References… and selecting the Microsoft Scripting Runtime library. Sub TestListFolders() [...]
The macro below shows how you can let the user select a single filename: Sub OpenOneFile() Dim fn As Variant fn = Application.GetOpenFilename(“Excel-files,*.xls”, _ 1, “Select One File To Open”, , False) If TypeName(fn) = “Boolean” Then Exit Sub ‘ the user didn’t select a file Debug.Print “Selected file: ” & fn Workbooks.Open fn End [...]
This example shows how you can use Microsoft Scripting Runtime to display information about all available drives (letter, type, free space e.t.c.). Microsoft Scripting Runtime is included in one of the following products: Windows98, Windows2000, IE5, og Office2000. Click here to download this file. Updated: 2001-07-28 Requires: XL97 File size: 31 kB
With the macros below you can print all workbooks in a selected folder. You have more control with what is printed than you have if you do this from Windows Explorer. Sub PrintAllWorkbooksInFolder(TargetFolder As String, FileFilter As String) ‘ prints all workbooks in a folder that matches the FileFilter ‘ example: PrintAllWorkbooksInFolder "C:\FolderName", "*.xls" ‘ [...]