3 things...PLEASE help...CSV/QUERY/CHART

[shmoussa]

Hi. I am fairly new to macros and visual basic. I hope someone can
steer me in the right direction with these three things I am looking
to accomplish.

FIRST__________

Every week a new .csv file needs to be imported into one table of
information. (One table contains weeks of data) The .csv files are all
in the same file location. Are any or both of these options
available:

1.) A command button that when pressed will bring up an open dialog
box, starting in a specified folder location. The user will then find
the .csv file and click open and the data will be imported
automatically. Afterwards, a dialog box confirming that the import
process is complete should pop up.

AND/OR

2.) The user will click an IMPORT button and it will automatically
find the most recent .csv file in a specified directory and import it.
Afterwards, a dialog box confirming that the import process is
complete should pop up.

I need to know if and HOW both of the methods are possible. If none
are possible, can you think of any other ways to do this. It needs to
be as AUTOMATIC as possible.

SECOND_____________

I have a listbox on my form, whose list of options (Volume Name) are
automatically generated based on a table. When I select one or more
Volume Names from the list- I need a query using criteria based on
that selection to automatically generate when a command button is
pressed. Is this possible?

THIRD_______________
If the above is possible, I would also like to know if it possible to
generate a chart, based on the query above. So basically, I need a
line graph to be generated from a query that is generated from a
listbox selection.

_______________________________________________

These three things would make my database as ideal for me and everyone
using it as possible. Please, if you have the correct code or method
to do this, share it. I would appreciate a reply as soon as possible.
Thank you.

 

[Mr. B]

Hi. I am fairly new to macros and visual basic. I hope someone can
steer me in the right direction with these three things I am looking
to accomplish.

FIRST__________

Every week a new .csv file needs to be imported into one table of
information. (One table contains weeks of data) The .csv files are all
in the same file location. Are any or both of these options
available:

1.) A command button that when pressed will bring up an open dialog
box, starting in a specified folder location. The user will then find
the .csv file and click open and the data will be imported
automatically. Afterwards, a dialog box confirming that the import
process is complete should pop up.

AND/OR

2.) The user will click an IMPORT button and it will automatically
find the most recent .csv file in a specified directory and import it.
Afterwards, a dialog box confirming that the import process is
complete should pop up.

I need to know if and HOW both of the methods are possible. If none
are possible, can you think of any other ways to do this. It needs to
be as AUTOMATIC as possible.

SECOND_____________

I have a listbox on my form, whose list of options (Volume Name) are
automatically generated based on a table. When I select one or more
Volume Names from the list- I need a query using criteria based on
that selection to automatically generate when a command button is
pressed. Is this possible?

THIRD_______________
If the above is possible, I would also like to know if it possible to
generate a chart, based on the query above. So basically, I need a
line graph to be generated from a query that is generated from a
listbox selection.

_______________________________________________

These three things would make my database as ideal for me and everyone
using it as possible. Please, if you have the correct code or method
to do this, share it. I would appreciate a reply as soon as possible.
Thank you.

First, let me suggest that you address one issue at a time. While
there's nothing wrong with asking multiple questions, it does cause
some of us to thing twice before attempting to answer a lot of
questions at one time,

With that said, I will attempt to try to help.

Concerning your first question: The importing of cvs files is most
certainly possible. Also, both of the options you describe can
actually be used to accomplish what you are describing. However, if,
as you describe, the cvs file are always going to be in the same
folder, then your second option is certainly the most simple way to
have the data imported.

First, I would suggest that when you are importing the data from one
of the cvs file manually, that you also create an Import Specification
for your cvs file. If there is only one file to be imported, then you
will only need to create one Import Specification. Once this Import
Specification is created, you can use it along with the "TransferText
acImportDelim" action to do the actual importing of the data.

The process you need will require the use of VBA. This code would be
in the Click event of the command button.

You do not indicate in your posting if you will be appending the new
data to the existing data in your table or if you are going to need to
empty any existing data from the table before importing the new data.

If you need to delete all existing records from the table then you
could use code like:

'empty the existing data from the "NameOfYourTable" table
strSql = "DELETE * FROM NameOfYourTable;"
CurrentDb.Execute strSql

You would, of course, replace the "NameOfYourTable" with the actual
name of the table where you want to import the data to.

Then you could actally do the import with code like:

'import the new data from the appropriate text file to the
"NameOfYourTable" table
DoCmd.TransferText acImportDelim, "Import_Specification_Name",
"NameOfYourTable", Complete Path to CVS file, False

In the code above, you would again replace the "NameOfYourTable" with
the actual name of the table where you want to import the data to.
Replace the "Import_Sepcification_Name" with the name you gave for the
import specification that you have created and replace the "Complete
Path to CVS file" with the actual complete path to the cvs file,
includiing the filename.

This is only a small portion of the code that would actually be
required if you are going to fully implement the automation of the
importing of these cvs files on a weekly basis. For example, you will
need to verify that the path and the file exists before you do the
import.

Hopefully this will get you thinking in the right direction.

As for question #2: The value held by a combo box can certainly be
used as the criteria for a query. However, the question comes to
mind, "What are you going to do with the results of the query you are
going to run?" You can bulid a query that will return the data you
are needing, then use the "Expresion Builder" to specify the name of
the form and the name of the combo box that you want to use as the
criteria for your query. In the QBE, place your cursor in the criteria
row of your query in the column of the field for which you need the
criteria. The right click and select the "Build" option. Use the
options from this dialog box to locate your form and then locate your
combo box. When you have these locate and added to the expression,
click ok and the expression will be add to your query, referencing
your form and the combo box. Then when you open your form, and make a
selection from the combo box then you can open your query and the
results will be filtered by the value in your combo box.

As for the chart, you ceretainly can create your chart and use a query
to provide the data to the chart.

Try resolving your first issue and then work on the others. As I
stated in the begining, you may get more assistance from the folks
here in the newsgroups if you attack one problem at a time.

Hope this helps you.

Mr. B

 

[shmoussa]

I appreciate your reply very much. Sorry I didn't split it up into
three questions. Didn't think of that.

As for my first question with importing CSV files....

1.) I think the best method for importing would be that when a user
clicks the IMPORT button on my form, a open dialog box will popup and
the user will find the CSV file themselves and click open. Then, to
automate the rest of the importing process I will use an import
specification. Could you help me figure out how to do that?

2.) The data in the CSV file will be added to an already existing
table. None of the information that is already in the table should be
replaced or deleted, only new information should be added. How do I
modify the code to do that?

3.) When importing data, is this possible: One of my field has names
like this in it (XX.XXXX.XXXX.XXXX). Is it possible to automcatically
modify this field to include nothing after the first decimal point? So
that only XX is placed in the field instead of the whole thing? Also,
before any of the fields I would like a column of data holding the
DATE that the file being imported was created. Can these be done
without having to go into the CSV and add the data manually to
hundreds of rows? Please guide me.

My second question about the query....

1.) I am using a multiselect box. Whatever fields I select in this
listbox I would like to have as the criteria for one of the fields in
a query. Between any of the selected things from my listbox I would
like the OR option typed.

list0 is my listbox
FORRRRRRRRRRRRRM is the query
TEXT is the textbox.
I want the information selected from the list to be the criteria for
the Volume Name field in the Disk Utilization Information Entry table

I tried to do this and just keep getting errors. I will post the code
in a bit.

 

[shmoussa]

I have my listbox query working now, using this code.




Private Sub cmdOpenQuery_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

For Each varItem In Me.list0.ItemsSelected
strCriteria = strCriteria & "[Disk Utilization Information Entry].
[Volume Name] =" & Chr(34) & Me.list0.ItemData(varItem) & Chr(34) & "
Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT [Disk Utilization].Date, [Disk Utilization ].[File
Name], [Disk Utilization ].[Volume Name], [Disk Utilization].[Space
Available], [Disk Utilization].[Space in Use] " & _
"FROM [Disk Utilization] " & _
"WHERE " & strCriteria

CurrentDb.QueryDefs("FORM").SQL = strSQL

DoCmd.OpenQuery "FORM"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection
First"
Exit Sub
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If

End Sub



Let's say this list has five options (A,B,C,D,E). If I select "A", I
also want the query to include "B". If I select "B" I want the query
to include "A"
What can I add to this code to do this??

I created another command button. How can I, when this button is
clicked, create a line graph using the query made from the selections
in this multiselect box?

Also- I have a single selection listbox. Let's say there are five
options (A,B,C,D,E). If I select "A," I want to open "QueryA." If I
select "B," I want to open "QueryB". What code can I use to do this?


Thank you.

 

[shmoussa]

I created another command button. How can I, when this button is
clicked, create a line graph using the query made from the selections
in this multiselect box?

I FIGURED THIS PART OUT....PLEASE HELP WITH MY OTHER QUESTIONS

 

[Mr. B]

I have my listbox query working now, using this code.

Private Sub cmdOpenQuery_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

For Each varItem In Me.list0.ItemsSelected
strCriteria = strCriteria & "[Disk Utilization Information Entry].
[Volume Name] =" & Chr(34) & Me.list0.ItemData(varItem) & Chr(34) & "
Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT [Disk Utilization].Date, [Disk Utilization ].[File
Name], [Disk Utilization ].[Volume Name], [Disk Utilization].[Space
Available], [Disk Utilization].[Space in Use] " & _
"FROM [Disk Utilization] " & _
"WHERE " & strCriteria

CurrentDb.QueryDefs("FORM").SQL = strSQL

DoCmd.OpenQuery "FORM"

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection
First"
Exit Sub
Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If

End Sub

Let's say this list has five options (A,B,C,D,E). If I select "A", I
also want the query to include "B". If I select "B" I want the query
to include "A"
What can I add to this code to do this??

I created another command button. How can I, when this button is
clicked, create a line graph using the query made from the selections
in this multiselect box?

Also- I have a single selection listbox. Let's say there are five
options (A,B,C,D,E). If I select "A," I want to open "QueryA." If I
select "B," I want to open "QueryB". What code can I use to do this?

Thank you.

Wow, if I thought that your first posting was asking multiple
questions, I just did not know what asking multiple questions was. :>
)

I will attempt to help your with some of these issues, however, I just
do not have enough time right now to address each and every one of
them.

1. From the way it sounds, you may need to have a table into which you
import your data from the csv file. The do some manipulation of the
data throught a query or two to another table to get the data into the
condition you want it.

As for allowing the user to select the file to import, check out:
http://www.mvps.org/access/api/api0001.htm

2. One of the reasons that I suggested that you have a table to
receive the data from the cvs file is so that after you have imported
the data and made modifications to it that you described is that then
you just create an append query that will add the newly imported and
modified data to the correct table.

3. During the process that you create to modify your data, you can add
a field to a query that will use the InStr function to locate the
first instance of the decimal (period) and use that in combination
with the Left function to return only the characters to the left of
the first decimal point for that field. Then that field would be one
of the fields that would get imported to the final table.

Also, for the date field. Do you require that each record being
imported have the date it was imported or do you just want know that
most recent date that data was imported. If you want each imported
record to have the import date in it, just add a date field to your
final table and set its default value to Now(). Then each time a
record is added the actual date and time of the creation of the record
will be entered.

I'm afraid I do not understand the issue with selecting A, B, etc.
Perhaps we can address this another time.

I',m out of time for now.

Hope this helps.

Mr. B