Results 1 to 8 of 8
Like Tree1Likes
  • 1 Post By Jon Peltier

CSV File to Excel File with Dynamic Graphing Capability

This is a discussion on CSV File to Excel File with Dynamic Graphing Capability within the Excel Questions forums, part of the Question Forums category; Hi I would like to know if my theory is possible for beginners like me in using VBA Goal: 1. ...

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    4

    Post CSV File to Excel File with Dynamic Graphing Capability

    Hi I would like to know if my theory is possible for beginners like me in using VBA

    Goal:
    1. To create an automatic converter of CSV File to Excel Worksheet
    ------------> Until this part, it is successful.
    2. To create graph according to the converted CSV File with the user only choosing the Type of graph to display and the Column for X and Y Values. This must be done also simultaneously with the conversion of CSV File to Excel
    ------------> This is the hard part and I dont know if its possible.

    Thank you!

  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,365

    Default Re: CSV File to Excel File with Dynamic Graphing Capability

    This is a great tutorial, but far too long for an answer here. I'll write it up on my blog next week.
    JCRon likes this.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  3. #3
    New Member
    Join Date
    May 2015
    Posts
    4

    Default Re: CSV File to Excel File with Dynamic Graphing Capability

    Hi Jon,

    That would be a really big help to me.

    I am looking forward for your tutorial.

    Thank you in Advance.

  4. #4
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,365

    Default Re: CSV File to Excel File with Dynamic Graphing Capability

    I'm working on it. The tutorial will probably be up early next week.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  5. #5
    New Member
    Join Date
    May 2015
    Posts
    4

    Default Re: CSV File to Excel File with Dynamic Graphing Capability

    Hi again Jon,

    I checked your blog and found that you are already finished with the tutorial.

    I am now currently indulging myself on how it works.

    This is really a big help.

    Thank you! :D



    PS: If anyone has the same problem with mine. Here's the link of the tutorial created by Jon Peltier.
    VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User - Peltier Tech Blog
    cheers guys!

  6. #6
    New Member
    Join Date
    May 2015
    Posts
    4

    Default Re: CSV File to Excel File with Dynamic Graphing Capability

    Oh Hi again!

    I have one last question.

    How about if my CSV File was delimited by Semi Colon?
    I tried changing this part: (On Parsing Part)
    Code:
    sTemp = sTemp & vRng(iRow, iCol) & ", "
    with
    Code:
    sTemp = sTemp & vRng(iRow, iCol) & "; "
    but with no luck. The converted csv file was still separated by semi colon.


    THANKSS!

  7. #7
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,365

    Default Re: CSV File to Excel File with Dynamic Graphing Capability

    Okay, so if it's delimited by semicolons, it's not a CSV file, since CSV = Comma Separated Values. Do everyone, including but not limited to yourself, a favor, and don't call a file a CSV file unless it's a Comma Separated Values file.

    So rename the file with a .txt extension, and make the following changes:

    Old code:
    Code:
      Dim sCSVFullName As String
      sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
          "Select a CSV file", , False)
      Dim wb As Workbook
      Workbooks.Open sCSVFullName
      Set wb = ActiveWorkbook
      Dim sWbkFullName As String, sFileRoot As String
      sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
    New code:
    Code:
      Dim sTXTFullName As String
      sTXTFullName = Application.GetOpenFilename("TXT Files (*.txt), *.txt", , _
          "Select a TXT file", , False)
      Dim wb As Workbook
      Workbooks.Open Filename:=sTXTFullName, Semicolon:=True
      Set wb = ActiveWorkbook
      Dim sWbkFullName As String, sFileRoot As String
      sFileRoot = Left$(sTXTFullName, InStrRev(sTXTFullName, ".") - 1)
    If you try to open a CSV file using Semicolon:=True, Excel will override this and only use commas. So all the more reason to not abuse the CSV definition.

    You could always use Text to Columns to get around the problem, but that leaves the problem in place.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  8. #8
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,365

    Default Re: CSV File to Excel File with Dynamic Graphing Capability

    I should note that the blog post from yesterday is a simple solution, and a more useful one will follow in another day or two. The better solution has some error checking and user-tedium-prevention things about it, though it would not have helped with the comma-semicolon issue.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com