Erlandsen Data ConsultingTime to upgrade an old popular code example, this time as a more complete example almost ready to use. You will still have to make a few decisions about what you actually want to import. But most of the work is done, so enjoy! Below is the main macro that will do most of the [...]
Updating a database with new records using separate INSERT commands for each new record can sometimes take very long time, especially if there are many thousands of new records to insert. One way to speed up this process can be to use the BULK INSERT command to update the SQL database in a significantly quicker [...]
The example function below can be used to retrieve contact information from your Outlook Contact folder, you only have to supply the contacts full name and the information you want the function to return. The function can be expanded to be able to return all stored contact information. Function GetContactInfoFromOutlook(strFullName As String, strReturnItem As String) [...]
This macro imports data from a delimited text file to a worksheet range: Sub ImportRangeFromDelimitedText(SourceFile As String, SepChar As String, _ TargetWB As String, TargetWS As String, TargetAddress As String) ‘ Imports the data separated by SepChar in SourceFile to ‘ Workbooks(TargetWB).Worksheets(TargetWS).Range(TargetAddress) ‘ Replaces existing data in Workbooks(TargetWB).Worksheets(TargetWS) ‘ without prompting for confirmation ‘ Example: [...]
This macro exports the values or formulas and charts from a worksheet range to a new workbook/worksheet: Sub ExportRangeAsWB(SourceRange As Range, TargetFile As String, SaveValuesOnly As Boolean) ‘ Exports the data in the range SourceRange to ‘ the workbook TargetFile in standard workbook format ‘ Examples: ‘ ExportRangeAsWB Range(“A1:M25″), “C:\FolderName\TargetWB.xls”, True ‘ ExportRangeAsWB Worksheets(“Sheet2″).Range(“A1:M25″), “C:\FolderName\TargetWB.xls”, [...]
The example macro below demonstrates how you can create a new PowerPoint presentation. Note! Read and edit the example code before you try to execute it in your own project! Sub CreateNewPowerPointPresentation() ‘ to test this code, paste it into an Excel module ‘ add a reference to the PowerPoint-library ‘ create a new folder [...]
If you want to export data to an Access table from an Excel worksheet, the macro example below shows how this can be done: Sub ADOFromExcelToAccess() ‘ exports data from the active worksheet to a table in an Access database ‘ this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, [...]
The procedure below can be used to create a dummy text file containing 1 000 000 semi-colon separated records: Sub CreateTextFileDB() Dim strTextFile As String, f As Integer Dim strItem1 As String, strItem2 As String Dim i As Long, j As Long strTextFile = “C:Temp” & Format(Date, “yyyymmdd”) & “.txt” On Error Resume Next Kill [...]
With the macros below you can read values and text from cells in closed workbooks. The example macros shows how you can read the value from cell A1 in Sheet1 in all workbooks in a given folder. Sub ReadDataFromAllWorkbooksInFolder() Dim FolderName As String, wbName As String, r As Long, cValue As Variant Dim wbList() As [...]
The example macro below shows how you can create a Pivottable in Excel based on data from an Access table or query. To run the example you will need an Access database with a table named “Customers” that contains the fields "CustomerID", “CustomerName”, “City”, “Region”, “Country”. Note! Read and edit the source code before you [...]