Converting Add() vba macro to OO

[Neil123456]

Hi

Can someone help me in converting the below macro to OO macro??

Code: Select all

Sub Add()

    Dim wb As Workbook
    
    ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
    Selection.Copy
    Set wb = Workbooks.Open(Range("H1") & "Database.xlsm")
    wb.Worksheets("Employee Details").Activate
    lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.Cells(lastrow1 + 1, 1).Select
    ActiveCell.PasteSpecial xlPasteValues
    ThisWorkbook.Activate
    ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
    Selection.Copy
    wb.Worksheets("Employee Details").Activate
    lastrow2 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    ActiveSheet.Cells(lastrow2 + 1, 2).Select
    ActiveCell.PasteSpecial xlPasteValues
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
    Set wb = Nothing
    
    Application.CutCopyMode = False
    ThisWorkbook.Activate
    ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
    Selection.ClearContents
    ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
    Selection.ClearContents
    
End Sub

I tried so much but i am not able to progress with changing it.
Please help me converting it.

[Bidouille]

You have a nice cross reference guide that will tell you what else you have to change apart from Worrkbooks.

For other question, you wil find resources on dedicated area: viewforum.php?f=20

[Neil123456]

Hi Bidouille

Thanks for reply....I have half of the functionality working and added few things; however, still I am not able to paste my data in the new file which was opened..the ActiveSheet command to calculate last row is not working and so i cannot go further. Here is the code below which is half way working if someone can help me proceed..

Code: Select all

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Sub Add()

    Dim Url
    Dim wb
    Dim oSheet As Object
    Dim oCurrentController As Object
    
    If Sheet3.Range("D6").Value = "" Then
        MsgBox "Please add Associate ID into Database"
    Exit Sub
    Else
        If Sheet3.Range("D8").Value = "" Then
            MsgBox "Please add Associate Name into Database"
        Exit Sub
        Else
            ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
            ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Copy
            Url = ConvertToURL(Sheet3.Range("H1").Value & "Database.xlsm")
            wb = StarDesktop.loadComponentFromURL(Url,"_blank",0,Array())
         	oCurrentController = wb.getCurrentController()
        	oSheet = wb.Sheets.getByName("Employee Details")
            wb.CurrentController.setActiveSheet(oSheet)
            lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
            ActiveSheet.Cells(lastrow1 + 1, 1).Select
            Worksheets.PasteSpecial xlPasteValues
            ThisWorkbook.Activate
            ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
            ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Copy
            wb1.CurrentController.setActiveSheet(oSheet)
            lastrow2 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
            ActiveSheet.Cells(lastrow2 + 1, 2).Select
            ActiveCell.PasteSpecial xlPasteValues
            ActiveWorkbook.Save
            ActiveWorkbook.Close
            
            Set wb = Nothing
            
            Application.CutCopyMode = False
            ThisWorkbook.Activate
            MsgBox "Associate Name - " & Cells(8, 4).Value & " & Associate ID - " & Cells(6, 4) & " Added Successfully"
            ThisWorkbook.Worksheets("Employee Addition").Cells(6, 4).Select
            Selection.ClearContents
            ThisWorkbook.Worksheets("Employee Addition").Cells(8, 4).Select
            Selection.ClearContents
        End If
    End If
End Sub

[Bidouille]

if someone can help me proceed.

Twice:

For other question, you wil find resources on dedicated area: viewforum.php?f=20

This is a Forum for OpenOffice. We are familiar with OpenOffice apps and its macro language(s). But if some of us know the macro language of MS Excel that's only by coincidence. So if you describe what your macro doing in a human language and if you provide a document, your chances that someone can help you are much higher.

[RoryOF]

The OO BASIC manual has some detailed exposition of Calc macros. Andrew Pitonyak has more.

[Neil123456]

I am just not able to pass this line for the above code...with this it goes back to the main document where the macro is
lastrow2 = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

[Zizi64]

How to determine the last used (non empty) row in the AOO/LO Calc:

viewtopic.php?f=21&t=96475
viewtopic.php?f=5&t=76415

[Neil123456]

The problem is not to get end row in the same sheet...but endrow in the new open sheet which is opened through the macro

[Zizi64]

endrow in the new open sheet which is opened through the macro

A new open sheet is empty with 0 used rows.

- You must know how many rows you paste/insert onto the sheet (by your macro)
- You must know which is the last used cell and column/row.
- If you not know them, then you can apply same method AFTER inserting/pasting the data - referencing to the new sheet.

[Neil123456]

That is what i am unable to do...if you can do it...please help me with writing the code in the above macro. Everything works fine before this line....lastrow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row...So, you can help me in replacing this line and further if possible.

[Villeroy]

This "conversion job" will never end. This bullshit code is a typical example of ridiculous VBA voodo copied and pasted mindlessly from stackoverflow.com

[Neil123456]

I am not tech savy....but if someone in the world has the power to do it...it will be helpful to everyone....the code is simple to copy text from cell...open another workbook...paste it in a cell of opened workbook and save and close...that's it

[Zizi64]

I am not tech savy....but if someone in the world has the power to do it...it will be helpful to everyone....the code is simple to copy text from cell...open another workbook...paste it in a cell of opened workbook and save and close...that's it

- "to copy text from cell...": From a selected cell? Always from same cell? Which Sheet from? The active one? From a Sheet with same name? From a sheet with same position?

- "of opened workbook": A new, empty wokbook? An exiting workbook? What is the URL of the existing workbook?

- "paste it in a cell" Which cell to paste the content to? A1, or other ? Which Sheet to paste the content to? The first one? Which part of the content need to paste to? The numbers, the texts, the formulas, the style, the other direct formatting properties?

- "save and close": If the workbook is a new workbook, then what will be the filename and the path?

- "the code is simple": really?

[Zizi64]

Partial sample codes:

viewtopic.php?f=45&t=21541
viewtopic.php?t=55228

[Neil123456]

- "to copy text from cell...": From a selected cell? Always from same cell? Which Sheet from? The active one? From a Sheet with same name? From a sheet with same position? yes from selected cell..yes always from same cell..sheet 1..yes active one where the macro is..sheet name can be different..yes same position or cell

- "of opened workbook": A new, empty wokbook? An exiting workbook? What is the URL of the existing workbook?..an existing xlsm workbook...on the desktop...yes an existing xlsm workbook

- "paste it in a cell" Which cell to paste the content to? A1, or other ? Which Sheet to paste the content to? The first one? Which part of the content need to paste to? The numbers, the texts, the formulas, the style, the other direct formatting properties?...yes A1 is fine can be changed later on..sheet 3 paste content...entire cell contents which are copied...can be number or text only

- "save and close": If the workbook is a new workbook, then what will be the filename and the path?...existing workbook which was opened should be saved...name can be changed

- "the code is simple": really? If a non technical guy like me can make such a macro on VBA and execute perfectly....i think you guys could play god with the oo vba

[Zizi64]

play god with the oo vba

OO VBA??? You must understand it: There is not (never was and never will be) such thing...
StarBasic (or an another supported programming language) and the API functions and objects. Those are existing things in the AOO/LO.

[Zizi64]

yes an existing xlsm workbook

Forget it!

You can not store AOO/LO macros in an OOXML type file.
You can not run AOO/LO macros from an OOXML type file.
You can not run VBA macros from an xlsm file efficiently in the AOO/LO.
You can not save the spreadsheet document into OOXML file format with the AOO (The LO can save into the OOXML format)


Use the ODF file formats, and the API functions of the AOO/LO for the macros.

[Villeroy]

Use Excel with your bullshit code -- forever Excel!

[Neil123456]

Ok...thanks for your reply....if there is anyone who can help??

[Zizi64]

if there is anyone who can help??

Yes. You are. Just study the linked code snippets, and the API.

[RoryOF]

Much of the code you need is in
Editing Spreadsheet Documents

[Villeroy]

Much of the code you need is in
Editing Spreadsheet Documents

That won't help because he his hung up at LastCell.End(xlUp) without having a clue about alternatives.

Most "VBA experts" try to force Excel doing database stuff. Converting this mess from Excel to any other spreadsheet is not an option. Developing a database solution would be an option.