Importing csv data

[schnett]

Hello,
I am importing csv data. I want to write a macro of a query to let the
user select which files to import and how many to import.

One example I would like my macro to do is the following :
1. There exist 3 csv files. The user imports all 3 files and puts them
on one worksheetsheet.
2. The macro formats the columns on the sheet.
3. Puts a check mark box over columns named A,B,C. To select or
deselect columns to have on graphs. Puts a checkmark in every box as a
default.
4. Sets up a x,y graph, graphing the checked columns as A(x),C(y). Sets
up another graph B(x),C(y)

Can you teach me or get me started on what I have to do for steps 1
through 4 ?

I have done formatting and graphing with a macro but never all at
once.

One of my main questions is : What if there is 2 csv files instead of 3
? How do I fix the error the macro gives me when it's looking for the
3rd file ?

I can clarify more if you need it. Thank you.

 

[funkymonkUK]

regarding your query about what if there are two instead of 3 files.
well do your files have a set name. eg. you get 3 files each month are
they sent in like FileA.xls, FileB.xls, FileC.xls or are they FileA
January 2006.xls fileb February 2006.xls etc?

 

[schnett]

Unfortuantely, they don't have a set name. I really would not like the
to either, if at all possible. These CSV files aren't generated by me
They are generated by various people. Thanks for responding

 

[Guest]

I would use an input box to ask the user how many CSV files have been imported.

expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile,
HelpContextId, Type)

As to for the graphs, you could record yourself creating a single graph and
use that code to figure out how to make the code work on different sheet
names. I usually create a generic graph and then use If statements to change
the Axis title locations or Y axis data locations.

Dim current
current = ActiveSheet.Name
Columns("B:h").Select
Charts.Add

ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(current).Range("B1:h20"),
PlotBy:= _ xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:=current
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasDataTable = False

Stuff like the following can be shoved into an If statement depending on
which Tab or type of graph you would like to produce. You can change a
generic graph to suit your needs.


ActiveChart.Axes(xlCategory).AxisTitle.Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=sheet1!R2C4:R50C4"
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlCategory).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = "=sheet1!R2C5:R50C5"



I sometimes use Strings to create modifications to my chart properties. It
allows me to use math or variables to make changes. I break the
"=combined!R2C5:R50C5" type addresses in to a bunch of modifiable strings.

Dim bottom
Dim combined
combined = "=combined!r"
Dim mid
mid = ":r"
Dim cend
cend = "c"
v11 = 11
v12 = 15
v31 = 9
v32 = 10
v21 = 22
v22 = 23
v23 = 24
v41 = 17
v42 = 18
v43 = 19
v44 = 20
v45 = 21
name1 = 3

bottom = 67 + (count - 2) * 46
Top = 101 + (count - 2) * 46


'assemble target cell ranges using variables, reusing variable names to make
programming easier.
v11 = combined & bottom & cend & v11 & mid & Top & cend & v11
v12 = combined & bottom & cend & v12 & mid & Top & cend & v12
v31 = combined & bottom & cend & v31 & mid & Top & cend & v31
v32 = combined & bottom & cend & v32 & mid & Top & cend & v32
v21 = combined & bottom & cend & v21 & mid & Top & cend & v21
v22 = combined & bottom & cend & v22 & mid & Top & cend & v22
v23 = combined & bottom & cend & v23 & mid & Top & cend & v23
v41 = combined & bottom & cend & v41 & mid & Top & cend & v41
v42 = combined & bottom & cend & v42 & mid & Top & cend & v42
v43 = combined & bottom & cend & v43 & mid & Top & cend & v43
v44 = combined & bottom & cend & v44 & mid & Top & cend & v44
v45 = combined & bottom & cend & v45 & mid & Top & cend & v45

name1 = combined & bottom & cend & name1 & mid & Top & cend & name1


ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Values = [v11]
ActiveChart.SeriesCollection(2).Values = [v12]
ActiveChart.SeriesCollection(1).XValues = [name1]
ActiveChart.SeriesCollection(2).XValues = [name1]
ActiveSheet.Select

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Values = [v21]
ActiveChart.SeriesCollection(2).Values = [v22]
ActiveChart.SeriesCollection(3).Values = [v23]
ActiveChart.SeriesCollection(1).XValues = [name1]
ActiveChart.SeriesCollection(2).XValues = [name1]
ActiveChart.SeriesCollection(3).XValues = [name1]
ActiveSheet.Select



Now that I have confused you, good luck.