CSV File to Excel File with Dynamic Graphing Capability

[JCRon]

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!

 

[Jon Peltier]

This is a great tutorial, but far too long for an answer here. I'll write it up on my blog next week.

 

[JCRon]

Hi Jon,

That would be a really big help to me.

I am looking forward for your tutorial.

Thank you in Advance.

 

[Jon Peltier]

I'm working on it. The tutorial will probably be up early next week.

 

[JCRon]

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!

 

[JCRon]

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)

[COLOR=#333333]sTemp = sTemp & vRng(iRow, iCol) & ", "[/COLOR]

with

[COLOR=#333333]sTemp = sTemp & vRng(iRow, iCol) & "; "[/COLOR]

but with no luck. The converted csv file was still separated by semi colon.


THANKSS!

 

[Jon Peltier]

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:

  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:

  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]

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.