csv files and macros

[chipperjones]

we generate a lot of reports from the as400 that are emailed in csv format.
yesterday we were asked to start running macros etc to generate bar graphs and such from these csv files before they are sent out to the user.

one example: report that shows production in our plants, they wanted to run macro on the spreadsheet that took the production numbers in the spreadsheet and then extrapolated a line graph from that.

we know how to create the csv file, we know how to do the macros in excel, what we do NOT know how to do is to generate this from the csv file without human interaction, without us having to open the csv, run macro, then email it.

what is everyone doing in this circumstance?
is there a way to perform these macros on the CSV file from the iseries without a user having to open them?

[GLS400]

Re: csv files and macros

Hi ChipperJones:

I'm sure you can do this with HSSF/POI processing. I'm doing it entirely in excel with macros.

' now open specified file name AND copy worksheet
    Workbooks.Open Filename:="H:\" & filnm & ".CSV"
    Application.DisplayAlerts = False
    
' copy the CSV worksheet
    Cells.Select
    Selection.Copy
    ActiveWorkbook.Close savechanges:=False
      

' open logo and paste to cell a13
    Workbooks.Open Filename:="H:\LOGO.XLS"
    Range("A13").Select
    ActiveSheet.Paste
    'Row(13)+ bold
  If NBRROW > "" Then
    NBRROW = NBRROW + 12
    Rows("13:" & NBRROW).Select
    Selection.Font.Bold = True
  If NBR14 > "" Then
    NBR14 = NBR14 + 12
    Rows("13:" & NBR14).Select
    Selection.Font.Size = 14
    NBR14 = NBR14 + 1
    Rows(NBR14 & ":" & NBR14).Select
    Selection.Insert Shift:=xlDown
    End If
    End If

    Cells.Select
    Cells.EntireColumn.AutoFit
    
' save logo as filnm.xls
    ActiveWorkbook.SaveAs Filename:="H:\" & filnm & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWorkbook.SendMail EMAILTO, "Deduction Report " & filnm
    ActiveWindow.Close
    
    Kill (DETAIL)
    DETAIL = "H:\" & filnm & ".CSV"
    Kill (DETAIL)
    DETAIL = "H:\" & filnm & ".xls"
    Kill (DETAIL)
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    Application.Quit
    
    ThisWorkbook.Close savechanges:=False

In the above I'm opening/copying the entire csv onto an XLS which already has our company logo.(logo ends in row 12)
I'm then saving the "logo" file as a seperate XLS.
You should be able to copy the csv, graph your cells, save as

Best of Luck
GLS