Ask Your Question

how to open multiple file through macro

asked 2015-12-17 06:27:22 +0200

Lion gravatar image

updated 2015-12-17 06:36:06 +0200

Hey Guys Can we open more then one file in macro Actually I want to compare 2 files and then need to set data everyday for 50 files, So manually It's taking much time So Is it any way to open 2 files and then compare both data and then it could be generate result file ?

Any help would be appreciate :)

Currently I am doing using this macro code, but it's open only one file then make new sheet and then copy data that open file to new sheet then hit some operation and finally generate output file So let me share here macro code of open file, make new sheet ,and last generate output file

    Sub ABCmacro

Dim oDoc As Object
Dim sUrl As String

Dim Prop(1) as New

Prop(0).value="Text - txt - csv (StarCalc)"

sUrl = convertToURL("D:\Projects\inputfile.csv")

if fileExists(sUrl) then
    oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank",0, Prop())
    msgbox "Not found"
end if

rem define variables
dim document as object
dim dispatcher as object

document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("")

rem ----------------------------------------------------------------------
dim args1(0) as new
args1(0).Name = "Name"
args1(0).Value = "Inputfile"

dispatcher.executeDispatch(document, ".uno:RenameTable", "", 0, args1())

dim args2(1) as new
args2(0).Name = "Name"
args2(0).Value = "Sheet2"
args2(1).Name = "Index"
args2(1).Value = 1

dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args2())

this is operation code (I am skipping that)
rem----------Output Coding--------------------------------

Dim Propval(1) as New
Propval(0).Name = "FilterName"
Propval(0).Value = "Text - txt - csv (StarCalc)"
Propval(1).Name = "FilterOptions"
Propval(1).Value = "44,34,76,1"   'ASCII  59 = ;  34 = "
Doc = ThisComponent
FileName = "D:\\Output_file.csv"
FileURL = convertToURL(FileName)
Doc.StoreAsURL(FileURL, Propval())

Now anybody got any idea that how to open 2 files instead of one

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-12-17 14:20:14 +0200

pierre-yves samyn gravatar image


1) How to open multiple file through macro?

Simple: 2 variables, 2 Url, 2 lines :

oDoc1 = stardesktop.LoadComponentFromURL(sUrl1, "_blank",0, Prop())
oDoc2 = stardesktop.LoadComponentFromURL(sUrl2, "_blank",0, Prop())

2) Open 2 files and then compare both data

You can use the EditCompare Document functionnality. In this case you do not need to "open" the 2 documents: open the first one, then launch Compare Document with the url of the second file as parameter.

Here is an example:

sub PysCompare

dim sUrl as string
dim oDoc as object, oDocFrame as object, dispatcher as object
dim PropVal(0) as new
dim args(0) as new

sUrl = convertToUrl("C:\Test\a.ods")

oDoc = stardesktop.LoadComponentFromURL(sUrl, "_blank", 0, Array())

oDocFrame = oDoc.CurrentController.Frame
dispatcher = createUnoService("")

PropVal(0).Name = "URL"
PropVal(0).Value = convertToUrl("C:\Test\b.ods")

dispatcher.executeDispatch(oDocFrame, ".uno:CompareDocuments", "", 0, PropVal())

args(0).Name = "ShowTrackedChanges"
args(0).Value = true
dispatcher.executeDispatch(oDocFrame, ".uno:ShowTrackedChanges", "", 0, args())

'dispatcher.executeDispatch(oDocFrame, ".uno:AcceptChanges", "", 0, array())'

end sub

The last statement is commented. It would accept changes directly. You just have to Save As to create a new result file.


edit flag offensive delete link more


Hi pierre-yves samyn , Can you tell me that how to compare with open 1 file only I have tried above code but it's not work How exactly compare , I mean what parameter It would take to compare In my case I have one common Column which has common data and I want to remove whole rows from one file which data has already in second file I hope that you would be gopt my Point

Lion gravatar imageLion ( 2016-03-01 13:26:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-12-17 06:27:22 +0200

Seen: 2,603 times

Last updated: Dec 17 '15