asked on

JAVA TO VBA conversion issue

I have converted a java program to VB program.

After converting, When I am trying to run the program, I am getting some errors. Please suggest if any help

Imports java.io.File
Imports java.io.FileNotFoundException
Imports java.io.FileOutputStream
Imports java.io.IOException
Imports java.sql.Connection
Imports java.sql.DriverManager
Imports java.sql.ResultSet
Imports java.sql.ResultSetMetaData
Imports java.sql.SQLException
Imports java.sql.Statement
Imports java.text.SimpleDateFormat
Imports java.util.Calendar
Imports java.util.LinkedHashMap
Imports java.util.Map
Imports java.util.Set
Imports java.util.TimeZone
Imports javax.activation.DataHandler
Imports javax.activation.FileDataSource
Imports java.util.Star
Imports javax.mail.Star
Imports javax.mail.internet.Star
Imports javax.activation.Star
Imports javax.mail.Session
Imports javax.mail.Transport
Imports org.apache.poi.hssf.usermodel.HSSFCell
Imports org.apache.poi.hssf.usermodel.HSSFCellStyle
Imports org.apache.poi.hssf.usermodel.HSSFFont
Imports org.apache.poi.hssf.usermodel.HSSFRow
Imports org.apache.poi.hssf.usermodel.HSSFSheet
Imports org.apache.poi.hssf.usermodel.HSSFWorkbook
Imports org.apache.poi.hssf.util.HSSFColor
Imports org.eclipse.swt.SWT
Imports org.eclipse.swt.ole.win32.OLE
Imports org.eclipse.swt.ole.win32.OleAutomation
Imports org.eclipse.swt.ole.win32.OleClientSite
Imports org.eclipse.swt.ole.win32.OleFrame
Imports org.eclipse.swt.ole.win32.Variant
Imports org.eclipse.swt.widgets.Display
Imports org.eclipse.swt.widgets.Shell
Public Class Check

Public Shared folder As String = "//Path//"

Public Shared filename As String = Nothing

Private hwb As HSSFWorkbook = Nothing

Private Shared con As Connection = Nothing

Public Shared Sub main(ByVal args() As String)
Dim pattern As String = "MMddyyyy"
Dim simpleDateFormat As SimpleDateFormat = New SimpleDateFormat(pattern)
Dim date1 As String = simpleDateFormat.format(New Date)
MsgBox(date1);
Dim file As File = New File((folder + ("\\" + date1)))
If Not file.exists Then
If file.mkdir Then
MsgBox("Directory is created!");
Else
MsgBox("Directory already exist!");
End If

End If

filename = (folder + ("\\" _
+ (date1 + "\\CHECK_SUMMARY_REPORT_")))
MsgBox("*********Start of Check*************");
Dim sqlExcel As Check = New Check
Try
con = sqlExcel.getDBConnection
'** SCRIPTS**
Dim sqlQuery1 As String = "query12"
Dim sqlQuery2 As String = "query11"
Dim sqlQuery4 As String = "query10"
Dim sqlQuery5 As String = "query9"
Dim sqlQuery6 As String = "query8"
Dim sqlQuery7 As String = "query7"
Dim sqlQuery8 As String = "query6"
Dim sqlQuery9 As String = "query5"
Dim sqlQuery11 As String = "query4"
Dim sqlQuery12 As String = "query3"
Dim sqlQuery13 As String = "query2"
Dim sqlQuery14 As String = "query1"
Dim rs As ResultSet = sqlExcel.getResultsetFromSql(sqlQuery1)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST1")
rs = sqlExcel.getResultsetFromSql(sqlQuery2)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST2")
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST3")
rs = sqlExcel.getResultsetFromSql(sqlQuery3)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST4")
rs = sqlExcel.getResultsetFromSql(sqlQuery4)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST5")
rs = sqlExcel.getResultsetFromSql(sqlQuery5)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST6")
rs = sqlExcel.getResultsetFromSql(sqlQuery6)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST7")
rs = sqlExcel.getResultsetFromSql(sqlQuery7)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST8")
rs = sqlExcel.getResultsetFromSql(sqlQuery8)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST9")
rs = sqlExcel.getResultsetFromSql(sqlQuery9)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST10")
rs = sqlExcel.getResultsetFromSql(sqlQuery10)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST11")
rs = sqlExcel.getResultsetFromSql(sqlQuery11)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST12")
rs = sqlExcel.getResultsetFromSql(sqlQuery12)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST13")
rs = sqlExcel.getResultsetFromSql(sqlQuery13)
sqlExcel.generateExcel(sqlExcel.processResultSet(rs), "TEST14")
rs = sqlExcel.getResultsetFromSql(sqlQuery14)
con.close
MsgBox("************End of CHECK**************");
Catch e As Exception
MsgBox(("Exception:" + e.getMessage));
End Try

End Sub

Public Function getDBConnection() As Connection
Dim conn As Connection = Nothing
Try
MsgBox("DB Connection got eshtablished..");
Class.forName("oracle.jdbc.driver.OracleDriver")

conn = DriverManager.getConnection(url1, "username", "password")
MsgBox("DB Connection eshtablished successfully!!");
Catch e As SQLException
e.printStackTrace
Catch e As ClassNotFoundException
e.printStackTrace
End Try

Return conn
End Function

Public Function getResultsetFromSql(ByVal sql As String) As ResultSet
Dim rs As ResultSet = Nothing
Try
Dim stmt As Statement = con.createStatement
rs = stmt.executeQuery(sql)
Catch e As SQLException
e.printStackTrace
End Try

Return rs
End Function

Public Function processResultSet(ByVal rs As ResultSet) As Map(Of String, LinkedHashMap)
Dim rowDetails As LinkedHashMap(Of String, String) = New LinkedHashMap(Of String, String)
Dim resultMap As Map(Of String, LinkedHashMap) = New LinkedHashMap(Of String, LinkedHashMap)
Dim rsm As ResultSetMetaData = Nothing
Try
If (Not (rs) Is Nothing) Then
rsm = CType(rs.getMetaData,ResultSetMetaData)
End If

Dim rowCount As Integer = 1

While rs.next
MsgBox("Records are Started Loading from DB to File");
Dim i As Integer = 1
Do While (i <= rsm.getColumnCount)
rowDetails.put(rsm.getColumnName(i), rs.getString(i))
i = (i + 1)
Loop

resultMap.put((New Integer(rowCount) + toString), rowDetails)
rowCount = (rowCount + 1)
rowDetails = New LinkedHashMap(Of String, String)

End While

Catch e As SQLException
e.printStackTrace
End Try

Return resultMap
End Function

' @SuppressWarnings("deprecation")
Public Sub generateExcel(ByVal resultMap As Map(Of String, LinkedHashMap), ByVal name As String)
Dim fileOut As FileOutputStream = Nothing
Try
Try
Dim cal As Calendar = Calendar.getInstance
cal.add(Calendar.DATE, 0)
Dim format1 As SimpleDateFormat = New SimpleDateFormat("dd-MM-YYYY HH")
Dim formatted As String = format1.format(cal.getTime)
Dim newfilename As String = (filename _
+ (formatted + ".xls"))
fileOut = New FileOutputStream(newfilename)
'sendEMail(newfilename);
Catch e As FileNotFoundException
e.printStackTrace
End Try

If (Me.hwb Is Nothing) Then
Me.hwb = New HSSFWorkbook
MsgBox("Created new book");
End If

Dim sheet As HSSFSheet = Me.hwb.createSheet(name)
' sheet.createRow((short)0);
Dim rMap As Map(Of String, LinkedHashMap) = resultMap
If (resultMap.get("1") Is Nothing) Then
MsgBox("No Data returned for this query");
Return
End If

Dim columnDetails As Map(Of String, String) = rMap.get("1")
Dim s As Set(Of String) = columnDetails.keySet
Dim cellNo As Integer = 0
Dim row As HSSFRow = sheet.createRow(cellNo)
Dim cellStyle As HSSFCellStyle = Me.hwb.createCellStyle
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN)
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN)
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index)
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
Dim font As HSSFFont = Me.hwb.createFont
'font.setBold(true);
cellStyle.setFont(font)
For Each s1 As String In s
Dim cell As HSSFCell = row.createCell(CType(cellNo,Short))
cell.setCellValue(s1)
cell.setCellStyle(cellStyle)
cellNo = (cellNo + 1)
Next
If (rMap.size > 0) Then
MsgBox((rMap.size + " Records loading got completed"));
End If

Dim i As Integer = 1
Do While (i <= rMap.size)
columnDetails = rMap.get((New Integer(i) + toString))
Dim nextrow As HSSFRow = sheet.createRow(i)
Dim set As Set(Of String) = columnDetails.keySet
Dim cellNum As Integer = 0
For Each s2 As String In set
nextrow.createCell(CType(cellNum,Short)).setCellValue(columnDetails.get(s2))
cellNum = (cellNum + 1)
Next
i = (i + 1)
Loop

Me.hwb.write(fileOut)
fileOut.flush
'fileOut.close();
Catch fe As FileNotFoundException
fe.printStackTrace
Catch e As IOException
e.printStackTrace
Finally
Try
Me.hwb.write(fileOut)
fileOut.flush
' fileOut.close();
Catch e As IOException
e.printStackTrace
End Try

End Try

End Sub
End Class

CEHJ

So why not

http://technojeeves.com/index.php/aliasjava1/17-errors

tell us what they are?

venkatesh Sarivisetty

ASKER

Not clear with your stament and link not working for me.. Here is my converted VB script. Please check and correct me.

gsk

Hi Venkat,

The System.out.print ln() method is for java not for VBA ,for Visual basic try

MsgBox("Message is") ; for your code above please try like
MsgBox("No Data returned for this query");

MsgBox("DB Connection eshtablished successfully!!")
use MsgBox every where if VB or Console.writeLine to debug your programme,
and if vb.net use Console.wrteLine("No Data returned for this query");

Hope it works

venkatesh Sarivisetty

ASKER

C:\Windows\System32\onlyIDTest.vbs(41, 8) Microsoft VBScript compilation error:
Expected identifier

gsk

please try

MsgBox("No Data returned for this query") // do at the all places

No semicolon at the end by the way which Vb version you are using or vb.net

venkatesh Sarivisetty

ASKER

I have pasted the code in notepad and saved it as .vbs

Creating a notepad like this and testing the script.

C:\Windows\System32\cscript.exe "onlyIDTest.vbs"
pause

venkatesh Sarivisetty

ASKER

Post removing semicolon also I am getting the same error

I am getting the same error at
C:\Windows\System32\onlyIDTest.vbs(41, 8) Microsoft VBScript compilation error:
Expected identifier

Public Class onlyIDTest at 41.

gsk

first tell me please are you using VB 6 or VB.net and if VB6 please use below programme to test first

Private Sub cmdClear_Click()
txtHello.Text = ""
End Sub

Private Sub cmdExit_Click()
End
End Sub

Private Sub cmdHello_Click()
txtHello.Text = "Hello World!"
With txtHello
.Font = "Arial"
.FontSize = 16
.ForeColor = vbBlue
End With
End Sub

check whether it runs

aikimark

Are you converting to VB.Net or VBScript?

venkatesh Sarivisetty

ASKER

No I am converting java program to VB script to make the Send mail can be done from VB ..

Thanks
Venkatesh.

venkatesh Sarivisetty

ASKER

How to know??
but I searched under control panel here is like this

Micorsoft .Net Framework 4.6.1
MS Visual C++ 2013 redistrubutable(3 versions) avilable.

venkatesh Sarivisetty

ASKER

When I tested your sample program, I got the below error

C:\Windows\System32\TESTING.vbs(6, 8) Microsoft VBScript compilation error: Expe
cted 'Sub'

gsk

Hi Venkatesh you are using VB.net not VB6
so please use Console.write Line Statement and compile it
it should run successfully

Bill Prew

@venkatesh,

The code you posted in your first comment is still a substantial mixture of syntax that will not work in VBscript. Some looks like maybe Java, some may pure VB. Either way, it's a long way off from being a workable VBscript code, and a larger effort than I can take on in answering a question...

How did you "convert" it to get to this point?

»bp

venkatesh Sarivisetty

ASKER

I used one site which will covert java to VB. if any chance, if any changes required to make it work on VB side, Please do the needful..

Thanks
Venkatesh.

Bill Prew

Well, that site didn't do a great job, but honestly it's not an easy conversion, especially given you are doing file and database access, and using some classes.

I'm sorry, but I don't have the time to do this conversion.

Here are a couple of references that will help you with major syntax differences between the languages, but other things like the language object model and reference java classes will need additional info. Good luck with your conversion.

»bp

aikimark

Please post your Java script.

gsk

Hi Venkatesh,

please use VB 6 if you using java to VB6 code conversion,I think you are using VB code and trying to run in VB.net framework.
Hope you are using VB.net framework,so please use either vb.net code or use vb editor.I hope you getting my point

venkatesh Sarivisetty

ASKER

Hi AikiMark, I have submitted my java program. Please check my quest.

Fabrice Lambert

Well, well, well .....

Java and VB* have absolutly nothing in common.
VB* lack constructors, lack lambdas, handle exceptions differently, have a limited support for inheritance, have different methods / properties calls, implement variables scope differently ect ect .... <insert nice Java Features here>

Trying to convert will be much more troublesome than anything else.
Your best bet is to understand the logic of the Java code, and write your own VBScript from scratch.

aikimark

I have submitted my java program. Please check my quest.

I do not see any Java code or attached file

venkatesh Sarivisetty

ASKER

Hi Alimark,

I am using this snippet to send the mail from java program. but it is not working and I am getting an error like ole client faile. Please suggest

public static void sendEMail(String rowdata1,String rowdata2,String drResult,String prodResult) {
Display display = Display.getCurrent();
Shell shell = new Shell(display);
OleFrame frame = new OleFrame(shell, SWT.NONE);
// This should start outlook if it is not running yet
OleClientSite site = new OleClientSite(frame, SWT.NONE, "OVCtl.OVCtl");
site.doVerb(OLE.OLEIVERB_INPLACEACTIVATE);
// Now get the outlook application
OleClientSite site2 = new OleClientSite(frame, SWT.NONE, "Outlook.Application");
OleAutomation outlook = new OleAutomation(site2);

Fabrice Lambert

Again, there is nothing in common between Java and VBA.

The code snipet you submited can be translated by the following:

Public Sub sendEMail(ByRef rowdata1 As String, ByRef drResult As String, ByRef prodResult As String)
    Dim outlook As Object
    Set outlook = CreateObject("Outlook.Application")

Open in new window

3 lines of codes in VBA, 7 in Java, see the differences ?

venkatesh Sarivisetty

ASKER

yes I Can see the ..
Can i share my complete java code, could you please help me on conversion?

CEHJ

Out of interest, why do you want it translated?

venkatesh Sarivisetty

ASKER

Exception in thread "main" org.eclipse.swt.SWTException: Failed to create Ole Client. result = -2147221164
      at org.eclipse.swt.ole.win32.OLE.error(Unknown Source)
      at org.eclipse.swt.ole.win32.OleClientSite.<init>(Unknown Source)

Then How I can fix this error? while sending mail using java program.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.