I’m going to provide a sample text file and a sample Excel VBA macro file for you to test. You can find the download below. Structured Data is Nice but Not Always Possible It would nice if every system had the ability to export data in more structured formats, like XML or json, rather than […]
Continue readingCategory Archives: Microsoft Access
Updated: Microsoft Excel VBA and Google Maps to calculate distances and time from multiple locations
Excel File download with VBA and Google Maps to calculate distance and duration between multiple destination addresses. Update 12/17/2019: Important! You must have a Google Matrix API Key and IT MUST BE CONNECTED TO AN API BILLING ACCOUNT! If your billing account is not active or connected to the API account, the tool WILL NOT […]
Continue readingMicrosoft Access and Word VBA Document Assembly System Demo
Summary: This demonstrates a methodology and some of the associated technologies I’ve used to create complex and flexible contract generators, proposal generators, and other document assembly systems. Technologies: Microsoft Access (SQL Server*) Microsoft Word – Word Templates – Word Global Template w/AutoText Entries * SQL Server: For simplicity, this demonstration uses Microsoft Access as the […]
Continue readingMoving From Excel to MS Access & SQL Server for Cleaning Up, Extracting, & Reporting on Data
Summary In our own work with data analytics, in coaching financial & business analysts, and with data conversions, we often coach clients to learn and use Microsoft Access or SQL Server, rather than (or perhaps more accurately, in conjunction with) Microsoft Excel. In this article, we explore some of the reasons why. “I suppose it […]
Continue readingVBA to Extract All Excel Formulas in a Workbook
Summary Some visual-basic for applications code to extract all the formulas from every sheet in an Excel Workbook and save them in a text file. Background Avoiding a long explanation, we’ve been writing a reporting tool that combines a Microsoft Access front-end to a SQL Server database. It generates and updates some Excel reports/data visualizations […]
Continue readingSharing Microsoft Excel Data Using SQL Server or Microsoft Access
Summary Demonstrate how data can be cleaned up and shared across multiple users while allowing reports and visualization in well-known and common tools. Technologies Microsoft SQL Server (Express) Microsoft Access & The Access Runtime Microsoft Excel Add-on: Microsoft Power BI The need Provide a data aggregation and cleanup tool with customized and shared Excel reports. […]
Continue readingListing Excel Workbooks and Sheets from Microsoft Access using VBA
SUMMARY: Tutorial to show how to use Microsoft Access VBA to retrieve and display open Workbooks and their Worksheets. I create a lot of dashboards, reporting systems, and document automation projects built extensively using Visual-Basic for Applications. Often, the infrastructure looks like: Microsoft Access as a front-end application. SQL Server as the back-end data repository. […]
Continue readingUsing SQL Server Express for Your Microsoft Access Projects
Summary: Some of the reasons why you should use SQL Server (or SQL Server Express) to store data for your Microsoft Access projects. Plus, code to simplify connecting to SQL Server and attaching tables without manual linking. The Access DB is miserable but awesome! Can we be honest. It’s slow, clumsy to move around, inherently […]
Continue readingMS Access – Passing a Variable and Unknown Number of Parameters to SQL Server
Summary How to avoid VBA errors with single quotes in a string being passed to a SQL Insert or Update Error. Includes how to passing parameters for an unknown or variable number of variables. Technologies Microsoft Access, SQL Server, ADO Something funny I hadn’t used this in a few years. When I ran across the […]
Continue readingParsing Quickbooks Customer (CustomerRet) Response using XML (DOMDocument)
As I indicated in my Quicbbooks Part 1 of never post, I would post some Microsoft Access and other Quickbooks source code and blog entries, rather than do a more detailed step by step explanation. I hope this is helpful. Quick Explanation You can parse Quickbooks response using a ICustomerRetList – and I may post that […]
Continue readingVBA solution: Scraping website information using MSXML, HTMLDocument, and getElementsByClassName
Summary An explanation of how to retrieve/scrape website data using MSXML and HTMLDocument but not using the InternetExplorer Application Object. The problem with the InternetExplorer.Application object I have a few blog entries demonstrating how to get web site data using InternetExplorer and the HTMLDocument object. However, there are a few problems with this: Microsoft is […]
Continue readingQuickbooks SDK iAddCheck using VBA. Add a check from Excel or Access
Someone reading my part 1 of ??? tutorial on Quickbooks – sorry.. read the notes. 😉 – was struggling to find how to add checks. I wrote them this morning about what to look for in the On Screen Reference OSR for the Quickbooks SDK. iCheckAdd Under the Onscreen Reference, there is a iCheckAdd message. […]
Continue readingRetrieve Data from Website in Microsoft Access or Excel using VBA
File Download: Microsoft Access/VBA Sample File Edit: 7/23/2017 – an improvement to this solution. This will require some refactoring. A week or so ago a reader asked (on this blog post), if we could place the file online that performs the operation in this blog post (and video). I’ve included it below as a download. The […]
Continue readingProgramatically retrieving data from a website into a database
Summary: Our client wanted to extract data from a web portal. We wrote a VBA script to the requested information and store it in an Access database. The example below demonstrates the same technology, but the data is stored in Excel. Edit: 7/23/2017 – An improvement on this solution. Edit: 9/9/2016 – I posted a download […]
Continue readingFounder’s blog – why we stopped selling social media
Pulse Founder, Matthew Moran, explains on his blog why we stopped selling social media to focus on database development and high-value information technology services. He also provides some advice on how you can fix your social media.
Continue readingMicrosoft Access, ADO, and Passing Parameters to a SQL Server
There are times you want to be able to run SQL Server commands – T-SQL Commands – with parameters from MS Access without necessarily having a stored procedure. Unfortunately, either through ignorance or some issue with the underlying technology, I was struggling with this recently. I’ll give you the solution first and back story later. […]
Continue reading