Auto import/link to CVS data

[Kicker]

Just looking for ideas and comments.....

At work, we use several reports created with Report Writer. Many of the people who NEED to use the data from the reports never look at the reports because of the nature of the way the report is designed. I have been playing with an excel spreadsheet that people are starting to like and even USE.

Here is what I have so far:
1. I run the RW report and export it to a CVS (comma delimited) file.
2. I Load my SSR_Manning excel workbook and clear the contents of the Data worksheet
3. I get external data and anchor it in the Data worksheet at $A$1.
4. Through the use of a simple sub routine, I distribute the data into specific cells in the ManningLevels worksheet.
5. Through the use of a complex public function, I calculate values for 10 days worth of shifts. Through the use of conditional formating for these cells, I change the interior.colorindex to black and create a type of GANTT chart.

This is working well. However.....To make it simple enough for the Shift Supervisors to update the data themselves, I would like to

Automate the import/link/whatever to the CVS file so that the newest and greatest data is automatically displayed. If it can be linked, it would save a lot of time. I know I can automate getting the external data.

Remember, the Shift Supervisors are the BEST at what they do, however, it took most of them 6-months to learn how to turn-on their computers (granted the classroom instruction was only 2 hours a day for the 6-months LOL).

ttfn
Kicker

[Kicker]

thanks, but I played with it long enough to come up with something that seems to be working pretty well.

ttfn
Kicker

[Zack Barresse]

Wow, that was fast! Would you mind telling us what worked for you?

[Kicker]

Not at all. I have gotten a LOT of help over the years from these forums and am glad to share whenever possible. This is a multipart problem. I just accepted a promotion and am now working in an Analysis/Resource Scheduler position. Corporate guidelines are very strict (and inforced) about what programs we may have on our computers. Even Access is not supplied to a person unless there is a demonstrated need over time.

I have ReportWriter with a limited number of fields from the master database and can export the report to a cvs file. I have an excel workbook with 2 worksheets.

I have created a userform and use it as a menu. One of the command buttons imports the cvs file and puts the data in the ImportData worksheet.

Private Sub cmdImportData_Click()
    Sheets("ImportData").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Charted").Select
    Call cmdUpdateGraph_Click
End Sub

There are several optionButtons to select a sort order and to select which Category of project I want to display on the chart. The tricky part was displaying the data on a 4-hour segment 3 shift per day chart that resembles a gantt chart. The following code actually distributes the data

Private Sub cmdUpdateGraph_Click()
Dim optChoice As String
Dim n As Long
Dim r As Long
Dim vCount As Long
Dim ssrNum As Long
Dim wrkSheet As String
    Sheets("Charted").Select
    If Me.optCAT10 Then optChoice = "10"
    If Me.optCAT20 Then optChoice = "20"
    If Me.optCAT30 Then optChoice = "30"
    If Me.optCAT40 Then optChoice = "40"
    If Me.optCAT50 Then optChoice = "50"
For n = 3 To 243 Step 5
        Range("A" & n & ":A" & n + 3).ClearContents
        Range("B" & n).ClearContents
        Range("B" & n + 1).ClearContents
        Range("B" & n + 2).ClearContents
        Range("B" & n + 3).ClearContents
        Range("C" & n + 3 & ":CH" & n + 3).ClearContents
    Next n
    r = Worksheets("ImportData").Range("B1").End(xlDown).Row
    ssrNum = 3
    vCount = 1
    For n = 2 To r
        If Mid(Worksheets("ImportData").Range("K" & n).Value, 1, 2) = optChoice Then
            Range("A" & ssrNum - 1).Value = str(vCount)
            vCount = vCount + 1
            Range("A" & ssrNum).Value = Worksheets("ImportData").Range("B" & n).Value
            Range("B" & ssrNum).Value = Worksheets("ImportData").Range("E" & n).Value
            Range("B" & ssrNum + 1).Value = Worksheets("ImportData").Range("I" & n).Value
            Range("B" & ssrNum + 2).Value = Worksheets("ImportData").Range("J" & n).Value
            Range("B" & ssrNum + 3).Value = Worksheets("ImportData").Range("C" & n).Value
            Range("C" & ssrNum + 3).Value = Trim(Worksheets("ImportData").Range("D" & n).Value) & _
                " -- " & Worksheets("ImportData").Range("P" & n).Value
            ssrNum = ssrNum + 5
        End If
    Next n
    Range("B253").Copy
    Range("C253:CH253").PasteSpecial
    Unload frmDateChange
    Range("A1").Select
End Sub

The attached file is a stripped down version with a lot of "test" code so don't laugh too much.

ttfn
Kicker