Macro from Excell not working, please help.

[Ridgh]

Hello,
I am using 2 PCs, using Windows 7 64 and 32.
Unfortunately, I know nothing about programming, and I need your help regarding a macro that worked in Excel 2010 on my Win 64 and 32, but now it is working only on the 32 version.
MS Office stopped working on the 64 PC, and I cannot reinstall it.
Basically, it uses 2 sheets. It is supposed to produce a shipping slip.
On the first sheet, I write the shipping address details.
I am clicking on the button "Save & Print", and this sheet is being send to the printer, and at the same time, the details are written on sheet 2, including the date and the product details (SET36 1958 08 March 2015) in sheet2.jpg.
I've copied the macro text (from Excel), and I am attaching it here.
Please help me to make it active on Open Office.
Many thanks in advance!


The Excel code:

Code: Select all

Sub Button1_Click()
    
Dim nCount As Integer
Dim strCell As String
Dim strName As String
nCount = Sheet2.Range("E2").FormulaR1C1
strCell = "A" & Trim(Str(4 + nCount))
Sheet2.Range(strCell).FormulaR1C1 = Now
strCell = "B" & Trim(Str(4 + nCount))
strName = Trim(Replace(Trim(Sheet1.Range("E6").FormulaR1C1), Chr(10), " / "))
Sheet2.Range(strCell).FormulaR1C1 = strName
strCell = "C" & Trim(Str(4 + nCount))
strName = Trim(Replace(Trim(Sheet1.Range("A26").FormulaR1C1), Chr(10), " / "))
Sheet2.Range(strCell).FormulaR1C1 = strName

nCount = nCount + 1
Sheet2.Range("E2").FormulaR1C1 = nCount
MsgBox "The receipt has been saved, and sent to printer", vbInformation
ActiveWindow.SelectedSheets.PrintOut
End Sub
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13/12/2006 by Miky
'

'
    Range("A7:E17").Select
    ActiveCell.FormulaR1C1 = _
        "Micronic1" & Chr(10) & " Michael Nxxxx" & Chr(10) & " 14611 Axxxx Str." & Chr(10) & " Sxxxx Oxxx, CA    91000" & Chr(10) & " United 

States" & Chr(10) & ""
    With ActiveCell.Characters(Start:=1, Length:=89).Font
        .Name = "Times New Roman"
        .FontStyle = "Bold"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With ActiveCell.Characters(Start:=90, Length:=1).Font
        .Name = "Times New Roman"
        .FontStyle = "Bold"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    
    ActiveSheet.Paste
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("D2").Select
End Sub

[Lupp]

Your Excel-macro uses different predefined names for objects and predefined names that have no direct equivalents in OpenOffice Basic. In addition the objects give access to their properties in different ways and under different names.

You may consider my opinion that VBA is made and Excel users are encouraged to rely on it as a means fighting against compatibility for commercial reasons.

As the decisiveness to support compatibility with Excel is stronger for the LibreOffice development than with Apache OpenOffice, you may try your sheets in a recent LibreOffice. It will probably work there without changes.

If I should try it for you, I need your spreadsheet document attached to your next post.

[Ridgh]

Thank you very much Lupp,
I tried to run it in LibreOffice. I received 2 errors:
1. You don't have security privileges (or something like this, I am not sure as it disappeared). I configured it to the lowest security option.
2. LibreOffice requires a 64-bit Java runtime environment (JRE) to perform this task. Please install a JRE and restart LibreOffice.

I am not sure what to do: should I download and install JRE from the official site?
Thanks again!

[RusselB]

Unlike OpenOffice (a 32 bit program), which requires a 32 bit version of the Java runtime even on 64 bit systems, LibreOffice has a 64 bit version and, therefore, requires the 64 bit version of the Java runtime.
You can easily, and with no trouble, have both 32 and 64 bit versions of Java installed on your system.
We always recommend downloading from official sites, which, for the Java runtime is https://www.java.com/en/download/

[Villeroy]

Your Excell macro is pointless. If you know templates with cell styles in either program, you can do the same without a single line of code.

[Lupp]

(The comment by @Villeroy obviously addresses the Sub 'Macro1' which is without any relevance anyway in the context.
It doesn't achieve anything useful and breaks the fundamental rule not to hard-code data into exacutable routines.
In addition the start of the range used there is merged into an area covering A6:A16... It's a mess. However, the vba code identifies A6 as the ActiveCell if run from LibO Basic with 'Option vbaSupport 1'. I would suppose it's the same if run from Excel-vba.
Simply enter the information into Sheet1.A6 and format it or let it be: the cell's area is not visible anyway, and its content is not stored.)

Concerning the routine 'Sub Button1_Click' now:

Facts:
It's an event handler not evaluating the event itself.
Its intended achievement is to read from exactly two cells in an area mainly prepared for printing a kind of shipping label, and to append the information (reworked into two one-line-texts) to a list maintained on Sheet2 enriched with a date-time-stamp in column A.

Criticism:
(The print is reported done before even tried.)
The constant "4" added to the number of already saved rows in sheet 2 should be "3".
Addresses and similar texts should always be composed from more basic ("atomic") data. The reverse way is deprecated for good reasons.
Never enter multy-line compounds for subsequent parsing/analysis.
Never use cell ranges formatted and optimised for printing additionally for data entry/editing/maintenance.
In a case where an address itself is containing a slash you cannot re-create the label from the stored list.

Suggestions:
Reorganise the document creating a sheet for data maintenance.
Select the label(s) for printing by pointing into that sheet (by indices most likely).
Pull the data needed into the variable cells of the printing area by simple spreadsheet formulae.
The printing itself and the amendment to the persistent list can then be processed with the help of a very simple Sub if more than one labels shall be printed in one go. The Sub (if needed at all) should be written in OpenOffice Basic using the API. Someone here might help. The printing of a single label can be triggered by a click on a standard icon of a toolbar then.
If there are stringent reasons not to create the maintenance sheet, create one little sequence of non-printing cells (outside the PrintRange) on Sheet1 to enter the variable data there, and use them by formulae then to get whatever you want to print.