Hello ? I am trying to make a program for work to graph some data a
machine outputs. I have already made the form in VB, and just need
some code to make it work. I am also including an excel file that I
made with the graph I made with the data.
In a nutshell, the program needs to find the *.txt file inputted by
the user, either through a direct text type-in in the text box
provided on the form, or the user can browse for the file in the c:\pl
folder or anywhere else if needed, but default first browse for file
in the c:\pl folder. These *.txt files are files with different names
outputted by the machines, so there won't be a set series name on it,
this is why we have to search or input search it.
This text file, whatever it is called, the one I am including is
called transitionsw-light2.txt ? this file is formatted 2 columns -
time(secs) & channelA.
I need the user to be able to click on ?Graph in Excel? and here?s
where the major coding begins ? the program needs to be able to fill
the data from the .txt file into a spreadsheet ? see the attached
graphing550nm.xls for an example of the layout, then somehow excel (or
vb ? I?m not sure how this would work so up to you however you think
is best) will graph this data ? column A and B in excel. See the
chart1 in excel for the graph I already made for layout. This is how
I would like the data to be filled in.
Then back to my VB form I made ? if the user clicks on ?save? the
program will save the form in excel ? I guess just bring up the excel
save function when this is clicked, so the user can define where
he/she would like to save it, and defaulting the save name to whatever
the *.txt name inputted to keep it simple ? but allow changes if
needed.
The ?view? button would just open the graph/spreadsheet in excel.
The ?print? button will print the graph/spreadsheet from excel.
The ?clear data? button will allow the user to do the graphing again
but with a different file without having to exit, start again. If the
user hasn?t saved yet, maybe have a prompt to ask them to save before
clear.
The user can end with the end button, also, if user hasn?t saved yet
give them an option to.
That?s it - Let me know if you need more information/clarification. Thanks!
Here are the files (it is off a free webhost on the web, so scroll
down to where it says "download free" for files, because it might look
like a giant advertisement at first since it has lots of banners to
allow it as a free service)
VB zipped files: http://rapidshare.de/files/5321903/GRAPHVB.zip.html
Excel File: http://rapidshare.de/files/5322089/graphing550nm.xls.html
Txt File: http://rapidshare.de/files/5322073/transitions_w-light_2.txt.html |
Request for Question Clarification by
nenna-ga
on
21 Sep 2005 21:11 PDT
Hi there -
Just wanted to let you know someone was working on this. I have the
program finished and should have it to you by late tomorrow night CST,
say midnight when I get home from the "day" job. I still have to get
it hosted and write up some information before I can submit the
answer.
Just a heads up.
Nenna-GA
|
Request for Question Clarification by
nenna-ga
on
21 Sep 2005 21:42 PDT
Also...
Is it essential that this is coded in a stand-alone Visual Basic
program, or would an Excel workbook that encapsulates all this
functionality be equally acceptable? A single workbook would be much
easier to work with, maintain, and expand because of Visual Basic for
Applications. A standalone Visual Basic project would be an
exponentially larger task to maintain and expand.
Let me know...
Nenna-GA
|
Clarification of Question by
kristalys-ga
on
22 Sep 2005 04:52 PDT
hi nenna - thanks - hmm - well i was hoping it would be one of those
programs where you could just click it and it takes you to somewhere
where you could put in the file name and hit a button and it does the
rest. if it can be done with excel/spreadsheet/vb application? or
however you were saying, then it's ok, but i was looking for something
where it could just standalone and be simple to read - especially in
case someone doens't exactly know how to navigate excel. thanks
|
Clarification of Question by
kristalys-ga
on
22 Sep 2005 05:12 PDT
if by expansion u mean adding more functionality to this program later
on - i don't think that's going to need to be. this program sort of
works hand in hand with another question about a data calculation
VB/Excel program - i posted on here ($200 bux for whoever answers it
:D + $75 tip before friday 9/23/05 (but will extend for monday) check
it out if you have time) that one needs to have the forms i made like
that - although i'm open to altering the program to work with excel if
need be, but as in both cases i need them both to be standalone - like
i could put it on my computer and just click the shortcut and the
program runs and storages the data in an excel spreadsheet. i need it
as simple to be run/user friendly. thanks :)
|
Request for Question Clarification by
nenna-ga
on
22 Sep 2005 07:28 PDT
Hey there!
Thanks for getting back to me, give me a day or 2 and I'll work on
this some more and report back to you with an answer.
Nenna-GA
|
Clarification of Question by
kristalys-ga
on
22 Sep 2005 08:30 PDT
hi nenna - thanks! one quick addition - the machine is now giving out
*.time files??? kind of weird, but if u open it with notepad it works
out as being a .txt file... this might need an extra step in the
program or if the program can somehow just use the .time file in excel
or wherever... i'm attaching it so you can see what i mean... hard to
explain.. thanks! http://rapidshare.de/files/5400951/patrick2.time.html
|
Request for Question Clarification by
nenna-ga
on
22 Sep 2005 11:23 PDT
The addition to this will not be an issue at all, thanks for letting me know.
Nenna-GA
|
Hi Kristalys-ga,
The file is hosted here:
http://pfile.net/gallery/v/Jennifer/Hosted/?g2_statusId=xd3459cbd&g2_navId=xc61d617f
It?s called grapher.xls I?ll be taking it down in a month or so, so
please get it saved to your hard drive.
This MS Excel workbook, called Grapher, is a read only file meant to
perform the actions you requested. It is a highly portable single file
and will work on any machine that has an installed copy of Microsoft
Excel. When a user opens the file, it's highly important their
security settings for Excel allow for macros to run. (In Excel 2003,
go to TOOLS
> OPTIONS > SECURITY > MACRO SECURITY > SECURITY LEVEL and set it to
"medium" to choose whether or not to run macros when an Excel file is
opened. Macros MUST be enabled for Grapher to work)
Grapher is NOT meant to store data, which is why it is read-only. It's
strict function is:
1) Import data 2) View data 3) Save data to new file.
-------------------------------------
Grapher contains only one sheet, called "Main". This sheet contains
captioned "form" buttons that perform the following functions:
File-->Graph*
View Graph
View Data
Print Graph
Print Data
Save
Clear Data
* No other button will work unless this one is performed first! The
requested "end" function is performed by closing the workbook, and
is therefore not included.
-------------------------------------
Each of these buttons on the sheet have a function that is described
below:
Function: File-->Graph
When you press this button, a dialog will open that first looks in
"c:\pl". If it cannot find this folder, it defaults to the current
directory set in the operating system. You can navigate anywhere on
your system. The two file formats you can look for is ".txt" and
".time" ? or "*.*" when you explicitly type it in. When you open the
chosen file, it is imported into Grapher as a second sheet with the
name of the file you chose (minus the extension). The data will be in
two columns.
Immediately after, a third sheet named "Graph" will appear, and this
sheet will contain the graph of the data that Grapher just imported.
When these actions are finished, you'll be returned to the main sheet,
called "Main". If this button is pressed again, it will not allow the
import of another data file. Only when Grapher contains the single
"main" sheet will it be able to import a text file. Saving the workbook
with these two new sheets inside will remain impossible because of the
nature of Grapher as a functional (not storage) workbook. If the
"read-only" attribute is removed from the file, you WILL be able to
save to the Grapher workbook, although this is not recommended.
Function: View Graph
(Works only after importing data) Press this to view the sheet, named
"Graph", which contains the graph
Function: View Data
(Works only after importing data) Press this to view the sheet, named
after your imported text file, which contains the data for the graph.
Function: Print Graph
(Works only after importing data) Press this to automatically print the
graph in the sheet, named "Graph", to the DEFAULT printer on your
system.
Function: View Graph
(Works only after importing data) Press this to automatically print the
data in the sheet, named after your imported text file, to the DEFAULT
printer on your system.
Function: Save
(Works only after importing data) When you press this, a dialog will
appear to save your file. The path is still where the operating system
was when we imported the file. The default file name is identical to
the file name of the imported text file. The only format you'll be
allowed to save in is XLS (an Excel workbook). When you are satisfied
with the location and name of the file that will contain the graph and
the data, press "save" in the dialog. The sheet named "Graph" and the
sheet named after your imported text file will be MOVED from Grapher
to this new workbook. The new workbook is then saved. Grapher will now
only contain the sheet named "Main,? and your new workbook will
contain the other two sheets. Grapher is now ready to import and graph
new data. When you open the workbook you saved earlier, the graph in
the sheet named "Graph" will still rely on the data in the sheet whose
name is identical to the new workbook's name.
Function: Clear Data
(Works only after importing data) Pressing this button will open a
message box that asks you if you want to save your "Graph" sheet and
the sheet named after the imported text file, which contains the data.
The choices will be "yes,? "no", and "cancel.? Pressing "cancel" only
cancels the operation. Pressing "no" will clear the two sheets from
Grapher. Pressing "yes" will allow you to save the sheets into a new
workbook (using the operation described above) before they are removed
from Grapher.
--------------------------
Please test this workbook to make sure that everything's working
properly. ( I already did and had no issues?but) A good test would
keep Grapher open while several imports and saves are performed on
your data. Try importing a TXT file, saving the results, clearing the
data, importing a TIME file, saving the results, and then doing the
same things without saving the results. If you experience no issues
and find it flexible enough, then Grapher is solid enough to perform
your requested tasks.
This should work well for you. If this answer requires further
explanation, please request clarification before rating it, and I'll
be happy to look into this further. Let me know if you find any bugs
as well and I?ll work on those and get you an updated version.
Nenna-GA
Google Answers Researcher |
Request for Answer Clarification by
kristalys-ga
on
25 Sep 2005 19:39 PDT
hi thanks so much, sorry i have been sick and out of the office for
the past few days, and haven't been able to test it. i will
definitely give it a shot tuesday, and let you know if i have any
questions, thank you so much :)
|
Request for Answer Clarification by
kristalys-ga
on
26 Sep 2005 06:27 PDT
hi nenna - how are you?
i ran the program on two machines today - my office 2003 works a.o.k.
- great! no questions there... however, my other computer - the one i
work with most of the time is still running office 2000 with windows
xp, and when i run your program i get the following error message -
"compile error named argument not found" and it highlights this line
of code "TrailingMinusNumbers:=True"
all macros are enabled with medium security.
i'm thinking this may be a compatibility issue between 2003 office and
2000 - is this so? and if so - please help - i greatly appreciate it
- thank you :) kris.
|
Clarification of Answer by
nenna-ga
on
26 Sep 2005 07:05 PDT
Not a problem at all..let me take a look at it here in the next day or
so and get back to you on what I can do.
Nenna-GA
|
Request for Answer Clarification by
kristalys-ga
on
27 Sep 2005 04:52 PDT
hello :) i also forgot to ask - how do i change the layout? say, if i
wanted to center the button or add a different font/color to the
instructions? i wanted to play around with this, but wasn't sure if
this would affect the code. thanks again! ~k.
|
Clarification of Answer by
nenna-ga
on
27 Sep 2005 09:39 PDT
Hi again,
Here ya go...
Problem 1:
As stated, the following error occurs when running this file on MS
Office 2000 - "compile error named argument not found" and it
highlights this line
of code "TrailingMinusNumbers:=True".
Fix to Problem 1:
The line of code that contains "trailingminusnumber:=true" needs to be
removed and has no adverse effects on the operation of Grapher. The
full line of code that shows up is:
Workbooks.OpenText Filename:= _
sFileName, Origin:= _
437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
TrailingMinusNumbers:=True
When you remove ", _ TrailingMinusNumber:=True", and save the
workbook, you should experience no further problems. You can remove
this easily by pressing "ALT F11" and doing a search for this code if
you cannot find it.
Problem 2:
As stated, you would like to manipulate the layout of the buttons and
would like to know how, and if it will affect the code.
Fix to Problem 2:
If you'd like to change the layout of the buttons, you need to be in
DESIGN MODE for Visual Basic for Applications.
To be in design mode, click "view > Toolbars > Visual Basic" to make
sure the Visual Basic toolbar is visible.
The toolbar contains an icon with a ruler and a pencil who's tooltip
text says "design mode". Click on this.
Now all your buttons will contain handles around the corners that
allow you to move and size the buttons.
Right-click on the button and select "properties" to change color and
captions, or any other properties.
To use the button's actual functions, click on the "design mode" icon
again to exit design mode.
The changes you make to the layout will not affect the code because
the code is an independent layer from the layout.
Be careful not to rename the main sheet, however. This is vital.
I hope this answers the question.
|
Request for Answer Clarification by
kristalys-ga
on
27 Sep 2005 11:40 PDT
hi nenna - thanks! i tried that - it gets rid of that bug, but a new
one appears - i'm getting this in excel 2000 -
"run time error '9' subscript out of range"
when i try to view data. the program then points to this line:
Sheets(sFileName2).Select
thanks - k.
|
Clarification of Answer by
nenna-ga
on
28 Sep 2005 12:35 PDT
The only way that I can replicate this issue is when I delete the
sheet with the data inside it. I have been unable to find a different
method of repeating this error. This error occurs because it is
looking for the sheet name that is NOT "Main" and NOT "Graph"....but
that sheet does not exist. Therefore, the code literally is doing
this "Sheets("").Select" which is impossible. If the sheet did exist,
and even if you renamed it to something completely different, it would
still find it and show it. I do not know how this error arose on your
system. This file runs perfectly on all 5 systems I have tested it
on. Also, the versions of Excel will not make a difference because
the code works the same through all the versions.
There is one way that this bug can be kept from showing up and causing
an issue on your system.
1)
Search for "BookHasGraph = True" in the code. A procedure named
"BookHasGraph" (in a module called modMain) should contain this code
right after a "Next" statement.
2)
Replace the line "BookHasGraph = True" with the following code:
Select Case sFileName2
Case "":
MsgBox "Your data sheet appears to be missing from
your workbook, although your graph is present.", vbOKOnly +
vbCritical, "Grapher"
BookHasGraph = False
Case Else:
BookHasGraph = True
End Select
If the sheet happens to be missing from your workbook for any reason,
a message box with the error above will show you what the issue is.
Nenna-GA
|
Request for Answer Clarification by
kristalys-ga
on
29 Sep 2005 06:22 PDT
hi! i got your message - and last question before you go.
what if i wanted to change the view of the graph to dots instead of a
line? or vice versa? what's the way to tweek the way a graph or chart
looks? please let me know - thanks - kris.
|
Clarification of Answer by
nenna-ga
on
29 Sep 2005 20:48 PDT
Kris,
That's a very good question. The code in Grapher is "hard coded" to
provide a graph that displays information in "scattered" form. In
order to change this, you'll need to visit the code by pressing
ALT-F11.
Look for the code in module "modMain" where you see the following:
' select data, insert chart
Columns("A:B").Select
Charts.Add
ActiveChart.ChartType = xlXYScatter
' give chart it's data
ActiveChart.SetSourceData Source:=Sheets(sFileName2).Range( _
"A:B"), PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Instant Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (Sec)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Channel A"
End With
This is the "meat" that really gets your graph into a sheet using the
data from your imported file. Notice the line "ActiveChart.ChartType
= xlXYScatter" is near the beginning. This line tells the macro what
type of chart to use. When you're in the code, try deleting " =
xlXYScatter" and type the "=" symbol. This will show a little dialog
window full of graph types that will enable you to choose what graph
you would like to display. You can select the type of graph in the
dialog window and press TAB, or you can type it in yourself. When you
run the macro, your preferred graph will display.
Another way to find out out your preferred graphing method is to
record a macro. Go to Tools > Macro > Record New Macro > (type in a
name) > OK. Create your preferred graph in Excel based off the data.
Stop the macro by hitting the "stop" button (the square in the
toolbar). Now hit ALT-F11 to access the code of the macro you just
recorded. Take a look at the Visual Basic code that recorded all your
actions. You should find "ActiveChart.ChartType = xlXYScatter"
somewhere in there, with "xlXYScatter" replaced with a different
designator. Play around and find the graph you want. Insert the
graph type into the code I've created for you. This will also get the
job done.
Finally, if you want to find out how to step through the code and
watch it work in real time, click on any line of code in the workbook
and hit F9. This will create a breakpoint by highlighting the line of
code in black. Whenever you press the buttons on the main sheet, the
code will pause when it hits this breakpoint (you can have multiple
breakpoints). You can then hit F8 for each line of code after that
and watch the code perform its actions in real time. This will be a
valuable way of learning how this workbook performs, and will add to
your understanding in case you would like to modify this in the
future.
This should show you two different ways of creating the types of
graphs you would like to have in your workbook. I hope this has been
helpful.
|
