When running Visual Basic for Applications code, there may be times when you need to open a CSV file as part of a procedure. A CSV, or Comma Separated Value file, is an Excel compatible file that has data separated by commas. A short piece of VBA code inserted into your script will open a CSV file with VBA.
When running Visual Basic for Applications code, there may be times when you need to open a CSV file to fill in data or make comparisons. VBA is designed to help you tailor Excel to suit your needs, and can be used to open practically any file on your computer, including CSV files.
In the early days of Microsoft Excel, the only way you could open a CSV file was by using a macro. A macro would record your keystrokes and you could replay them back at a later time. With the advent of VBA, you can still record a macro to open a CSV file -- which will create VBA code in the background -- but you also have the option of going into the Visual Basic Editor and editing the code yourself.
Accessing the Visual Basic Editor
There are two primary ways to access the VBE: through the ribbon, or through a keyboard shortcut. Access the Visual Basic Editor by clicking the "Developer" tab, then clicking "Visual Basic," or press "Alt" and "F11" together. Once you've opened the VBE, you can either open an existing piece of code by double clicking the name in the left hand column, or open a new window by clicking "Insert," then "module."
The following code will open a CSV file in Excel:
On Error Goto 1 ActiveWorkbook.FollowHyperlink "C:\\my document.csv", NewWindow:=True Exit Sub 1: MsgBox Err.Description
Before running the code, change the path in line three to the actual path of your file.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for