PDA

View Full Version : Solved: Auto import/link to CVS data



Kicker
08-28-2004, 10:14 AM
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
08-28-2004, 05:05 PM
thanks, but I played with it long enough to come up with something that seems to be working pretty well.

ttfn
Kicker

Zack Barresse
08-29-2004, 11:50 AM
Wow, that was fast! Would you mind telling us what worked for you?

Kicker
08-29-2004, 08:05 PM
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