Welcome to the portion of my website where I share and store information or tools about Microsoft Access and Excel. Hope you find some of it interesting or helpful!
If you're totally new to using Microsoft Access, I recommend reading this article to help get you started.
If you have any questions or suggestions for the articles on the website, feel free to post in the forums. I can't promise my response will be quick, but I will try my best to respond to all forums posts.
Sincerely,
Azli
As an Amazon Associate I earn from qualifying purchases.
Suppose you have a workbook with a ton of cell comments, but you want to give a copy to someone without all the comments. Here's some simple code to remove all comments from a workbook. In this case, I assume it the ActiveWorkbook.
If you get a "Unable to Reference DAO Object Library" or "Method MethodName of Object ObjectName Failed" error message when trying to use DAO VBA code in Access 2016 / 365, but you've set a reference to the DAO 3.6 Object Library, or if you're trying to add a reference to the DAO 3.6 Object Library but Access won't let you; then the issue is that you're already using DAO but not with the DAO 3.6 Object Library.
Turns out that DAO is now part of "Microsoft Office 16.0 Access database engine Object Library".
So, make sure you're using the appropriate reference for your version of Access, i.e. Microsoft DAO 3.x Object Library (Access 97 - 2003) or Microsoft Office 1x.x Access database engine (Access 2007 onwards)).
VBA UDF (User Defined Function) to determine if a file name is valid, e.g.
If IsValidFileName("test.txt") Then
Msgbox "File name is valid"
Else
Msgbox "File name is NOT valid."
End If
I was working on some code that would save an Excel file and thought it would be good to check if the file name was actually a valid name. So, I found an article about naming files and used the info in it to write a formula to check if a file or folder name is a legal file name. I was thinking whether to name it 'IsValidFileFolderName' or 'IsValidFileName'. I decided on the shorter version. See the quote below for why.
From 'Naming Files, Paths, and Namespaces:
Note that a directory is simply a file with a special attribute designating it as a directory, but otherwise must follow all the same naming rules as a regular file. Because the term directory simply refers to a special type of file as far as the file system is concerned, some reference material will use the general term file to encompass both concepts of directories and data files as such. Because of this, unless otherwise specified, any naming or usage rules or examples for a file should also apply to a directory.
The code below takes a string and determines if it is a legal file name or folder name.
You can create a table using SQL. You will have to write the SQL for the query yourself as there is no built-in wizard for this.
Here is an example of how to create a table named 'tblSQLMadeMe'.
[NOTE: The data definition language (DDL) CREATE TABLE statement (or any DDL statement, for that matter) only works with Microsoft Access database engine databases.]