Macro from Excell not working, please help.

Creating a macro - Writing a Script - Using the API

Macro from Excell not working, please help.

Postby Ridgh » Fri Dec 28, 2018 2:54 am

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!
Image
Image
The Excel code:

Code: Select all   Expand viewCollapse view
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
Last edited by robleyd on Sat Dec 29, 2018 12:18 am, edited 1 time in total.
Reason: Added Code tags
Open Office 4.15
Windows 7 64
Ridgh
 
Posts: 2
Joined: Fri Dec 28, 2018 2:04 am

Re: Macro from Excell not working, please help.

Postby Lupp » Fri Dec 28, 2018 2:46 pm

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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2618
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Macro from Excell not working, please help.

Postby Ridgh » Fri Dec 28, 2018 9:18 pm

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!
Open Office 4.15
Windows 7 64
Ridgh
 
Posts: 2
Joined: Fri Dec 28, 2018 2:04 am

Re: Macro from Excell not working, please help.

Postby RusselB » Fri Dec 28, 2018 11:31 pm

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/
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5799
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Macro from Excell not working, please help.

Postby Villeroy » Fri Dec 28, 2018 11:42 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27579
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro from Excell not working, please help.

Postby Lupp » Sat Dec 29, 2018 5:07 pm

(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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2618
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests