Though I try to avoid it, I occasionally have to open a CSV file in Excel. When I do, it formats columns containing numbers, which makes them useless for my purposes. VBA A Simple Tool to Open a CSV File and Create Chart with Data Specified by User. In a recent Mr Excel post, a member asked how to convert a CSV File to Excel File with Dynamic Graphing Capability. This is a great topic for a tutorial, but too long for a forum answer. Problem Statement. The simple statement was this Convert a CSV file to an Excel workbook. Create a chart based on. User specified chart type. User specified columns for X and YI expanded on this a little bit Open a user selected CSV file. Save as an Excel workbook. Display dialog for user to select. Chart type. Columns containing X and Y values for chart. Create the desired chart. This is a pretty easy project, which would generally take me half a day or less, depending on other distractions. Approach. Essentially, I started with this simple VBA procedure, and a similarly simple User. Form dialog. Sub Open. CSVFile. And. Plot. Data. 1. Get CSV file name. Open CSV file. 3. Save as workbook. Parse file. 5. Show dialog select chart type, X values, Y values. Draw chart. End Sub. StyleSheet for use when a translation requires any css style changes. This StyleSheet can be used directly by languages such as Chinese, Japanese and Korean. My first time through development, I did the least I had to do to make it work. The second time through, I added a bunch of nice embellishments that should make things easier for users to just pick it up and run with it. For both levels, the following sections have pieces of code describing what is happening, the complete code, and an example workbook. Building a Simple Solution. Code Workbook. Im using a macro enabled Excel workbook named MyCSVDataProcessor. It has a single worksheet names Parameters. Saved Information Named Range Containing Chart Types. The worksheet contains a list of chart types that our program will create. These are the simplest and most useful charts built into Excel. The list has two columns, the first being the integer values of Excels chart type constants e. Area 1, xl. XYScatter 4. These are some common FLTK code snippets I often find hard to remember how to do when looking at the docs. Besides, I like having working code examples handy. Excel VBA Open Workbook Easily Open Excel Files In VBA With These 2 Methods And Macros. By Jorge A. Gomez. VBA Code Excel Macro Examples Useful 100 Macros, Codes and How Tos explained Basic Beginners, Advanced users. Learn Excel 2003, 2007, 2010, 2013 Macros. The range A2 B1. Chart. Types, so it will be easy for the code to put these values into the dialog. Starting the Program Form Control Button. To add a button, the Developer tab must be visible on the ribbon. KB/vista/VGFileDialogs/filesavedlg.png' alt='Getopenfilename Default File Path Too Long How To Fix' title='Getopenfilename Default File Path Too Long How To Fix' />If you dont see it, right click on a blank place in the ribbon, choose Customize the Ribbon. In the list on the right Main Tabs, click the box in front of Developer. On the Developer tab, in the Controls group, click the Insert dropdown, then under Form Controls, click on the first icon, Button. X6JnoL0U4BY/S-eiSJWOnvI/AAAAAAAAeY8/Zn3qRhtaxaI/tmp3384_thumb_thumb.jpg?imgmax=800' alt='Getopenfilename Default File Path Too Long How To Fix' title='Getopenfilename Default File Path Too Long How To Fix' />Draw a button on the worksheet. A dialog will pop up asking which VBA procedure to assign to the button you can revisit this dialog by right clicking on the button. Select Open. CSVFile. And. Plot. Data and press OK. Then type a caption on the button and resize it as needed. Code Module. We need to put the code into a regular code module. In the VB Editor, find the workbooks project in the Project Explorer pane, right click anywhere on it, and choose Insert Module. When a new code module opens up in the editor, rename it to something useful like MChart. From. CSVFile using the Properties pane. The exact name you use isnt critical, but it should be somewhat descriptive. On the View menu, click Project Explorer CtrlR or Properties F4 if these panes are not visible. Getting Ready. The first line of the module should be. Option Explicit. If this statement doesnt appear, type it in, then go to Tools menu Options, and check the Require Variable Declaration box. While youre in the Options dialog, uncheck Auto Syntax Check. After a blank line or two, type Sub and the name of the procedure. The VB Editor automatically skips another line and types End Sub for you Sub Open. CSVFile. And. Plot. Data. End Sub. The main program will be typed between Sub and End Sub. All the variables will be declared at the top using Dim statements. Variables dont need to be declared at the top of a routine, but VBA offers no just in time benefit to declaring them right where you start using them, and I like having them in one place so I can find their declarations easily. In the sections below I will insert a simple Dim statement for the variables used. Get CSV File Name. We can use Excels Get. Open. File. Name to allow the user to select a CSV file from his computer. Dim s. CSVFull. Name As String. CSVFull. Name Application. Get. Open. FilenameCSV Files Select a CSV file, FalseWeve specified the CSV file filter, indicated a title for the Open File dialog, and said False to selecting multiple files. Open CSV file. This part is easy, we just need to open the file named in the previous step. Then well set a workbook object variable to this file, so its easy to reference later in the code. Dim wb As Workbook. Workbooks. Open s. CSVFull. Name. Set wb Active. Workbook. 3. Save as workbook. We need to remove the file extension from the CSV file name which includes the file path, which we do by retaining only text up to the last period in the file name. Then we add the Excel workbook file extension, and save the file, specifying the default workbook format. Dim s. Wbk. Full. Name As String, s. File. Root As String. File. Root Lefts. CSVFull. Name, In. Str. Revs. CSVFull. Name,. 1. s. Wbk. Full. Name s. File. Root. Save. As s. Download Splinter Cell Double Agent Ps2 Iso Files. Wbk. Full. Name, xl. Workbook. Default. Parse file. Now the file is open, so well extract some information to place in the dialog. Well use a two column display, where the first column has the column number, and the second a simple comma delimited list of the first few values in that column. Dim ws As Worksheet, rng As Range, v. Rng As Variant. Dim i. Rows As Long, i. Cols As Long, i. Row As Long, i. Col As Long. Dim s. Temp As String. Dim v. Chart. Data As Variant. Set ws wb. Worksheets1. Set rng ws. Used. Range. v. Rng rng. Value. 2. i. Rows rng. Rows. Count. i. Cols rng. Columns. Count. info to display column number, first few rows of column. Re. Dim v. Chart. Data1 To i. Cols, 1 To 2. For i. Col 1 To i. Cols. v. Chart. Datai. Col, 1 i. Col column number. For i. Row 1 To 4 first 4 values. Temp s. Temp v. Rngi. Row, i. Col ,. Temp Lefts. Temp, Lens. Temp 2 remove last comma. Chart. Datai. Col, 2 s. Temp. Next. 5. Show dialog. We need to instantiate the User. Form i. e., load it into memory, pass in the array of column numbers and initial column values, and display the form. At this point, code stops and waits for the user to make his selections and dismiss the form. When the form is dismissed, we need to get the users selections chart type, whether the CSV file has header labels in the first row, and the columns to be used for X and Y values in the chart. Then we remove the User. Form from memory. Dim b. First. Row. Headers As Boolean. Dim my. Chart. Type As Xl. Chart. Type. Dim v. X As Variant, v. Y As Variant. Dim frm. Chart. From. CSVFile As FChart. From. CSVFile. Set frm. Chart. From. CSVFile New FChart. From. CSVFile. With frm. Chart. From. CSVFile. Chart. Data v. Chart. Data. get information selected by user. Chart. Type. Chart. Type. b. First. Row. Headers. First. Row. Headers. v. X. Xcolumns. Y. YColumns. Unload frm. Chart. From. CSVFile. Draw chart. We need to figure out how to separate the data into separate series, then we need to get the data into the chart. In XY charts, we let the user create one or more series, where a all series share the same set of X values or no X values if the user didnt specify them, and Excel will use 1, 2, 3, etc. X values and each series has a unique set of Y values, b all series share the same set of Y values and each has a unique set of X values, or c each series has its own unique sets of X and Y values. For other chart types, the only relevant combination of X and Y is a, since Excel uses the same X values for all series regardless of how we specify them. We will deal with this in the Advanced version of this program. Excel 2. 01. 3 introduced Add. Chart. 2 as an improvement over Excel 2. Add. Chart method. Add. Chart is hidden in Excel 2. Add. Chart. 2 will crash Excel 2.