I want to generate excel files automatically, some files generated by same crystal report, but different parameter. then I used for and select. but when the files are generated in the for, if no records the file become empty, no title, no anything. I cannot solve it. who can help me?
Module AutoInterface
Public Sub AutoInterfaceDriver()
Dim sModuleName As String = "AutoLogin"
' Initial Log files
goLog = New LogAdapter(sModuleName)
For i As Integer = 0 To 1
Dim sAutoCompanyID As String = My.Application.CommandLineArgs(i)
Dim dt As DataTable
Try
goLog.Append("Get auto-login particulars for """ & sAutoCompanyID & """.")
dt = gdbGeneral.AutoLogins.GetAutoLogin(sAutoCompanyID)
If dt.Rows.Count = 0 Then
Throw New Exception("Auto-login setting has not been set for company id """ & sAutoCompanyID & """.")
Else
' Get Auto login particulars
goAccpac.UserID = dt.Rows(0).Item("USERID").ToString
goAccpac.Password = dt.Rows(0).Item("USERPSW").ToString
goAccpac.SessionDate = Date.Today
goAccpac.AccpacDB = sAutoCompanyID
' Login into Accpac
goAccpac.Connect()
goLog.Append("Auto-login successful.")
End If
dt.Dispose()
AutoPrint("WIP-FINISH")
AutoPrint("WIP-RAW")
AutoPrint("MI631016")
AutoPrint("Intercompany-StockBalance")
AutoPrint("SCRAP-WEBOPEN")
AutoPrint("MI631006")
Catch ex As Exception
goLog.Append(ex.Message)
goLog.Append(ex.ToString)
End Try
Next
End Sub
Public Sub AutoPrint(ByVal rptName As String)
Try
Print_UsingCrystalReportNet(rptName)
Catch ex As Exception
goLog.Append(ex.ToString)
End Try
End Sub
Sub Print_UsingCrystalReportNet(ByVal rptName As String)
Try
Dim rpt As New ReportDocument
With rpt
' Load the report
.Load(My.Application.Info.DirectoryPath & "\Reports\" & goAccpac.AccpacDB & "\" & rptName & ".rpt", CrystalDecisions.Shared.OpenReportMethod.OpenReportByDefault)
For i As Integer = 0 To .DataSourceConnections.Count - 1
.DataSourceConnections.Item(i).SetConnection("", gdbAccpac.DSNName, False)
Next
' Verify the database
.VerifyDatabase()
If (Not System.IO.Directory.Exists(My.Application.Info.DirectoryPath & "\XLS\" & goAccpac.AccpacDB & "\")) Then
System.IO.Directory.CreateDirectory(My.Application.Info.DirectoryPath & "\XLS\" & goAccpac.AccpacDB & "\")
End If
End With
' Set parameters
Select Case Split(rptName, "-")(0)
Case "WIP"
rpt.SetParameterValue("YEAR", DateTime.Now.Year.ToString())
rpt.SetParameterValue("PERIOD", DateTime.Now.Month.ToString())
Case "TOOL"
rpt.SetParameterValue("From Completed Date", (DateTime.Now.AddDays(-DateTime.Now.Day + 1)).ToString("yyyy-MM-dd"))
rpt.SetParameterValue("To Completed Date", (DateTime.Now.AddMonths(1).AddDays(-DateTime.Now.AddMonths(1).Day)).ToString("yyyy-MM-dd"))
Case "SCRAP"
rpt.SetParameterValue("From Completed Date", (DateTime.Now.AddDays(-DateTime.Now.Day + 1)).ToString("yyyy-MM-dd"))
rpt.SetParameterValue("To Completed Date", (DateTime.Now.AddMonths(1).AddDays(-DateTime.Now.AddMonths(1).Day)).ToString("yyyy-MM-dd"))
Case "Intercompany"
rpt.SetParameterValue("Please select Date", (DateTime.Now.AddMonths(1).AddDays(-DateTime.Now.AddMonths(1).Day)).ToString("yyyy-MM-dd"))
Dim myParameterFields As ParameterFields
myParameterFields = rpt.ParameterFields()
If (goAccpac.AccpacDB.Equals("HTSFP")) Then
SetDateRange(myParameterFields, "BT", "Z1")
ElseIf (goAccpac.AccpacDB.Equals("HTSCO")) Then
SetDateRange(myParameterFields, "BT", "TRM")
End If
End Select
If rptName.Equals("MI631016") Then
Dim strRNG2 As String = String.Empty
For i As Integer = 1 To 7
With rpt
.SetParameterValue("CMPNAME", goAccpac.Session.CompanyName)
.SetParameterValue("QDEC", "6")
.SetParameterValue("MDEC", "6")
.SetParameterValue("TDEC", "6")
.SetParameterValue("ITEMIDFMT", "%-24C")
If (goAccpac.AccpacDB.Equals("HTSFP")) Then
Dim sfItemFrom = Heitkamp.My.Settings.HTSFP_ItemFrom.ToString()
Dim sfItemTo = Heitkamp.My.Settings.HTSFP_ItemTo.ToString()
.SetParameterValue("FROMITEM", sfItemFrom)
.SetParameterValue("TOITEM", sfItemTo)
ElseIf (goAccpac.AccpacDB.Equals("HTSCO")) Then
Dim coItemFrom = Heitkamp.My.Settings.HTSCO_ItemFrom.ToString()
Dim coItemTo = Heitkamp.My.Settings.HTSCO_ItemTo.ToString()
.SetParameterValue("FROMITEM", coItemFrom)
.SetParameterValue("TOITEM", coItemTo)
End If
Select Case i
Case 1
.SetParameterValue("RNG2LO", "WH1")
.SetParameterValue("RNG2HI", "WH4")
strRNG2 = "WH1TO4"
Case 2
.SetParameterValue("RNG2LO", "S1")
.SetParameterValue("RNG2HI", "S1")
strRNG2 = "S1"
Case 3
.SetParameterValue("RNG2LO", "R1")
.SetParameterValue("RNG2HI", "R5")
strRNG2 = "R1TO5"
Case 4
.SetParameterValue("RNG2LO", "PC1")
.SetParameterValue("RNG2HI", "PC5")
strRNG2 = "PC1TO5"
Case 5
.SetParameterValue("RNG2LO", "OF")
.SetParameterValue("RNG2HI", "OF")
strRNG2 = "OF"
Case 6
.SetParameterValue("RNG2LO", "TATA")
.SetParameterValue("RNG2HI", "TATA")
strRNG2 = "TATA"
Case 7
.SetParameterValue("RNG2LO", "")
.SetParameterValue("RNG2HI", "ZZZZZ")
.SetParameterValue("FROMITEM", Heitkamp.My.Settings.ZZZZZ_ItemFrom.ToString())
.SetParameterValue("TOITEM", Heitkamp.My.Settings.ZZZZZ_ItemTo.ToString())
strRNG2 = "ZZZZ"
End Select
Dim timefile = DateTime.Now.Date.ToString("ddMMyyyy")
.ExportToDisk(ExportFormatType.Excel, My.Application.Info.DirectoryPath & "\XLS\" & goAccpac.AccpacDB & "\" & rptName & "_" & strRNG2 & "_" & timefile & ".XLS")
'Threading.Thread.Sleep(5000)
goLog.Append(goAccpac.AccpacDB & "\" & rptName & "_" & strRNG2 & "_" & timefile & ".XLS. Auto Created.")
End With
Next
ElseIf rptName.Equals("MI631006") Then
Dim strRNG2 As String = String.Empty
For i As Integer = 1 To 6
With rpt
.SetParameterValue("CMPNAME", goAccpac.Session.CompanyName)
.SetParameterValue("QDEC", "6")
.SetParameterValue("MDEC", "6")
.SetParameterValue("TDEC", "6")
.SetParameterValue("ITEMIDFMT", "%-24C")
.SetParameterValue("FROMITEM", "")
.SetParameterValue("TOITEM", "ZZZZZZZZZZ")
Select Case i
Case 1
.SetParameterValue("RNG2LO", "BT")
.SetParameterValue("RNG2HI", "BT")
strRNG2 = "BT"
Case 2
.SetParameterValue("RNG2LO", "CS")
.SetParameterValue("RNG2HI", "CS")
strRNG2 = "CS"
Case 3
.SetParameterValue("RNG2LO", "ME")
.SetParameterValue("RNG2HI", "ME")
strRNG2 = "ME"
Case 4
.SetParameterValue("RNG2LO", "TRM")
.SetParameterValue("RNG2HI", "TRM")
strRNG2 = "TRM"
Case 5
.SetParameterValue("RNG2LO", "WTP")
.SetParameterValue("RNG2HI", "WTP")
strRNG2 = "WTP"
Case 6
.SetParameterValue("RNG2LO", "MT")
.SetParameterValue("RNG2HI", "MT")
strRNG2 = "WT"
End Select
Dim timefile = DateTime.Now.Date.ToString("ddMMyyyy")
.ExportToDisk(ExportFormatType.Excel, My.Application.Info.DirectoryPath & "\XLS\" & goAccpac.AccpacDB & "\" & rptName & "_" & strRNG2 & "_" & timefile & ".XLS")
'Threading.Thread.Sleep(5000)
goLog.Append(goAccpac.AccpacDB & "\" & rptName & "_" & strRNG2 & "_" & timefile & ".XLS. Auto Created.")
End With
Next
Else
Dim timefile = DateTime.Now.Date.ToString("ddMMyyyy")
rpt.ExportToDisk(ExportFormatType.Excel, My.Application.Info.DirectoryPath & "\XLS\" & goAccpac.AccpacDB & "\" & rptName & "_" & timefile & ".XLS")
'Threading.Thread.Sleep(5000)
goLog.Append(goAccpac.AccpacDB & "\" & rptName & "_" & timefile & ".XLS. Auto Created.")
End If
Catch ex As Exception
goLog.Append(ex.Message)
goLog.Append(ex.ToString())
End Try
End Sub
Private Sub SetDateRange(ByVal myParameterFields As ParameterFields, ByVal startDate As String, ByVal endDate As String)
Dim myParameterRangeValue As ParameterRangeValue = New ParameterRangeValue()
myParameterRangeValue.StartValue = startDate
myParameterRangeValue.EndValue = endDate
myParameterRangeValue.LowerBoundType = RangeBoundType.BoundInclusive
myParameterRangeValue.UpperBoundType = RangeBoundType.BoundInclusive
Dim myParameterField As ParameterField = myParameterFields("Location")
myParameterField.CurrentValues.Clear()
myParameterField.CurrentValues.Add(myParameterRangeValue)
End Sub
End Module
Submit an article or tip