October 2020
How To Use The VBA Input Box Cancel Button To Exit Sub
This post will demonstrate how you can use the VBA input box cancel button to exit the sub procedure. When you click the “Cancel” button on the input box, you return a null (blank) value, and knowing this information, you can exit the sub procedure. Here is an example: Sub VBAInputBoxCancel() Dim strResponse As […]
How To Search A String With The VBA Instr Function
The VBA Instr Function is very useful for searching a string for a particular piece of text. If for example you want to loop a column or a field in a table of multiple rows, and want to identify prize rows that are 81.51, you would write something like this: Sub VBAInstrFunction() Dim dblTemp As […]
How To Make An InputBox In VBA
The input box is a great way to retrieve and process input from your user. In the following example we’ll control program flow based on the user’s input in the inputbox. Your user will enter a text value and we will evaluate their answer and send them in the right direction. Sub InputboxVBA() Dim strAnswer […]
Bringing A 300+ Column Excel File Into Access
Someone asked an interesting question about Excel and Access, so I decided to transcribe it here. “I have an excel file with 300+ columns. I would to split and export them to access as two table and join them. Could you please help me with this.” Thanks in advance, Ma…. Here was my response: “Hi […]
How To Return A Dollar Value As A Text String
Hi, I received an interesting request, involving VBA and how to return a stock price dollar value in a text character string rather that actual numbers. Like (I=1,M=2,E=0) instead of showing cost price is 12.00 it shows IM.EE. Kind of like code returning a special code. Well, here is my rendition of it. It may […]
Access Pagination
I have a client who has an underperforming continuous form so I start tinkering around with a model, and created an Access continuous form with pagination abilities. The database is attached. Hopefully it can help you. I will probably be modifying it as needed. [sourcecode language=”vb”] Option Compare Database ‘code 2014 by http://loeblcomservices.com ‘contact: erik@loeblcomservices.com […]
MS Access Hiding Text Boxes Based On User Selection VBA
I received an email from a guy to the effect of wanting to hide some textboxes based on a value entered into a single textbox. In my answer, I came up with a way to do this, and decided to share it with you… This example will show you how to hide the textboxes on […]
How To Write A Recordset To File With VBA
In this video you’ll find out how to write a recordset to a file with VBA using the Microsoft Scripting Object. You’ll find out how to take your table data, and export it to a csv formatted file so you can share it with other applications. Use this method to get your data out of […]
VBA Building Dynamic SQL
This video example is going to show you how to generate a SQL string from a form in VBA. [sourcecode language=”vb”] ‘======================================================== ‘DATABASE DESIGNED & CODED BY LOEBLCOM SERVICES 2013 ‘ERIK LOEBL(713)409-7041 ‘EMAIL: erik@loeblcomservices.com ‘WEB: http://loeblcomservices.com ‘======================================================== Option Compare Database ================ form: ================ Private Sub btnFindStocks_Click() Dim strWhere As String Dim strSQL As String Dim […]
Excel VBA Read Text File
So how can you import the contents of a csv or text file into your Excel worksheet: Find out: ‘======================================================== ‘DESIGNED & CODED BY LOEBLCOM SERVICES 2013 ‘ERIK LOEBL(713)409-7041 ‘EMAIL: erik@loeblcomservices.com ‘WEB: http://loeblcomservices.com ‘======================================================== Sub query_text_file() ‘PURPOSE: Query A Text File ‘************************************* ‘clear ‘Text File Contents’ worksheet ‘************************************* Worksheets("Text File Contents").Select Range("A2:K6500").Clear ‘************************************* ‘now populate […]