If you want to delete all or some of the tables from your Access database, you can use this DAO approach. You would need a reference to Microsoft DAO 3.x object library. As shown in the example, you can use an array to store the table names, which you want to keep or delete. Sub […]
Continue readingIf you would like to link all Dbase files, any linkable file for that matter, in MS Access, use the following code. I read somewhere that refreshing the links is slower than deleting and creating new links. Sub LinkAllTblsinDir() Dim sTblNm As String, sPath As String, sFileNm As String sPath = “C:\DW\” ‘Turn of the […]
Continue readingIf you want get or print file names from a certain directory, then you can use following code. Sub GetFileNames() Dim sPath As String, sFileNm As String sPath = “C:\DW\” ‘You can also use Application.GetOpenFilename to get a file name from a folder, ‘and then extract the Directory name from that string ‘You can also […]
Continue readingProblem Are you frustrated that you have to concatenate a big range of cells in Excel, but you have to do so using CONCATENATE function by entering each cell and typing a comma after every cell? Well, here’s a solution to it. A procedure or a function whatever you like. If you use the procedure, […]
Continue readingIf you want to do a text search count in a range, you can use the following formulas:Where D3 is the search text and the range A2:A16 houses the data. An array formula using SUM and SEARCH {=SUM(IF(ISERROR(SEARCH(D3,A2:A16)),0,1))} Another using COUNTIF: =COUNTIF(A2:A16,”*”&D3&”*”) This picture might make it clear:
Continue reading