Help with Visual Basic in Excel

I am new to the whole Visual Basic and Excel programming. I was looking for some help to point me in the right direction with a program I'm supposed to figure out. The goal is to take a Report run in a program called MKS and have it save a new file (with the file name as the week# in the year) onto my computer, each time I tell the report to run. I have dummy code I'm trying to work with (below) but other than that I don't know how to edit this to get the file I want, or how to start from scratch... Any help would be more than greatly appreciated!

Thank you!


Sub MetricsMain()

Dim i As Integer
Dim mydate As Date ' Variable that holds the current date
Dim CurrentYear As String ' Contains the current Year
Dim WeekNumber As String ' Contain the ISO Week Number
Dim OutputFileName As String ' Contains the name of the Integrity Manger Report file created
Dim CommandLine As String ' Shell Command String
Dim DataFileName As String ' Name to the report file created by Integrity Manager
Dim CommandLineArray() As MetricsInfo

Application.ScreenUpdating = False
Call ReadIndexMaster(CommandLineArray)


' Get Today's Date

mydate = Date

' We need to compute the curret yeat and ISOWeekNumber.
' These are used to name the report file

CurrentYear = Year(mydate)
WeekNumber = ISOWeekNum(mydate, 1)
DataFileName = "WK " & WeekNumber & ".htm"



For i = 1 To UBound(CommandLineArray)
Application.StatusBar = False
OutputFileName = Chr(34) & CommandLineArray(i).ReportFilePath & "\" & CurrentYear & "\" & DataFileName & Chr(34)
Application.StatusBar = "Running Report: " & CommandLineArray(i).IM_Report

CommandLine = "im runreport --forceConfirm=yes --outputFile=" & OutputFileName & " " & Chr(34) & CommandLineArray(i).IM_Report & Chr(34)
Call HardShell(CommandLine)

' If the Metrics file is archived in Source Integrity, then it needs to be checked out before we update the metrics

If CommandLineArray(i).Sandbox <> "" Then
CommandLine = "si co --cwd=" & Chr(34) & CommandLineArray(i).Sandbox & Chr(34) & " --changePackageID=:none --yes " & Chr(34) _
& CommandLineArray(i).MetricsFileName & Chr(34)
Application.StatusBar = "Checking out: " & CommandLineArray(i).Sandbox & "\" & CommandLineArray(i).MetricsFileName
Call HardShell(CommandLine)

End If

Application.StatusBar = "Computing Metrics: " & CommandLineArray(i).ReportFilePath & "\" & CurrentYear & "\" & DataFileName
Call PerformMetrics(CommandLineArray(i), CurrentYear, DataFileName)

' If we checked the file out, we need to check the file back in with the changes

If CommandLineArray(i).Sandbox <> "" Then
CommandLine = "si ci --cwd=" & Chr(34) & CommandLineArray(i).Sandbox & Chr(34) & " --changePackageID=:none --yes " & _
"--description=" & Chr(34) & Left(DataFileName, Len(DataFileName) - 4) & " Updates" & Chr(34) & " " & Chr(34) & CommandLineArray(i).MetricsFileName & Chr(34)
Application.StatusBar = "Running Report: " & CommandLineArray(i).Sandbox & "\" & CommandLineArray(i).MetricsFileName
Call HardShell(CommandLine)
End If
Next i
Application.ScreenUpdating = False

End Sub

Bump

Hi,

The above code works with a 3rd party app. If you are starting with zero VB knowledge, you may have a hard time. The code provided is only partial code as I see calls to other code not provided.

If you want to get this complete in an acceptable timeframe, contact the 3rd party app vendor for support, they know their product best.

Also, does your IT department not support these programs; normally this code would be locked down so the end user does not have access - just curious.