Macro to open file and then tabulate/graph the data contained.

[Jacek]

Hello,

As I have recently been notified that I will need to learn some VBA coding for a potential employment opportunity, I've been pretty much scrambling to figure out some important features I am required to know. Your assistance is greatly appreciated.

What I am trying to do is have a macro (activated by one of those custom buttons we can create) request the user to select a file. This I was able to do as I found a straightforward script on the internet.

My problem is...I want to then have the rest of the macro take that opened file and format it (i.e. tabulate the data on one worksheet, generate some plots on a second and state averages/max/mins/etc on a third worksheet.

I was able to do the second half of the macro using the Macro Recorder - all I did was record me opening the file (a .dat file to be precise), formatting it appropriately, adding the table details, plots and voila...it could play it all back. Of course at that point, the macro only ran for that specific file. I want to provide the user the ability to pick any .dat file in our directory before having the macro spit out a legible display of the information stored in the .dat file.

Again...I'm going to be reading into VBA/macro writing throughout the week. I have experience with C++, basic stuff...so I do understand the general UI with VBA and the basic syntax rules.

Any help is well appreciated. Thank you.

Jacek

 

[Jacek]

Bump, anyone?

 

[Jon Peltier]

This tutorial shows how to display to the user a File Open dialog, and how to make use of the filename selected by the user:

Get Open or Save-As Filename

 

[Jacek]

This tutorial shows how to display to the user a File Open dialog, and how to make use of the filename selected by the user:

Get Open or Save-As Filename

Thanks Jon. I will try this out right away and let you know how it goes. I appreciate the prompt reply.

 

[Jacek]

This is what I have so far:


Sub OpeningAFile()

Dim Test As Variant

Test = Application.GetOpenFilename(, , "OPEN FILE", , False)

If TypeName(Test) = "Boolean" Then
MsgBox "You Closed"

Else

Workbooks.Open (Test)

End If

End Sub


Now everything works fine when I test any kind of file...the only exception is when I try to open a .dat file...it simply opens it in a non formatted way. As in, the little dialog/formatting wizard pop up doesn't show up to let me describe how the information in the .dat file itself is organized. Is there any way I can have that dialog pop up?

Any help is well appreciated.

Thank you!

 

[Jon Peltier]

I've corrected the WordPress malformatting on my blog, so now you can read the code samples on the page I referenced.

Instead of Workbooks.Open, you need to use Workbooks.OpenText. You'll have to check the VBA help for the right syntax for your file, or record a macro while you open the file and format the text import. Here is syntax for a tab delimited text file:

Workbooks.OpenText Filename:="C:\Users\Me\Documents\mydata.dat", DataType:=xlDelimited, Tab:=True

 

[Jacek]

I've corrected the WordPress malformatting on my blog, so now you can read the code samples on the page I referenced.

Instead of Workbooks.Open, you need to use Workbooks.OpenText. You'll have to check the VBA help for the right syntax for your file, or record a macro while you open the file and format the text import. Here is syntax for a tab delimited text file:

Workbooks.OpenText Filename:="C:\Users\Me\Documents\mydata.dat", DataType:=xlDelimited, Tab:=True

Amazing! Thank you so much Jon, I was able to base if off of the comma delimited condition. I also managed to insert a recorded macro code of me creating a table of the data, adding headers and titles. Thank you so much!

I've got one more question...I would like to generate some plots of the data but on a separate worksheet in the same workbook that opens...any suggestions? Of course I will continue to make my own efforts in searching these answers up online. I will also try to muck around with the recorder again.

Thank you thus far, your help has been invaluable.

Jacek

 

[Jon Peltier]

Don't forget, these are not official workbooks, they are text files. You can create charts, but when you go to save, you will be warned about incompatibilities with the format you're saving, blah blah.

So step 1 is to save as an Excel workbook.

Then you need to insert the worksheet that will contain your charts.

Then you will insert your charts on this sheet, identifying the data for each.

At least this is the flow I envision, but you may have different plans. You can get most of the pieces using the macro recorder, then by asking in this forum. If the topic is different, start a new thread.