Remove Rows from Excel with Xojo

General
1

By using the example database, there are many ways to handle Microsoft Excel documents.

I was wondering if there is a way to remove rows from Excel?

We receive a Excel file that needs to be move to another directory, rename and opened.

Once open, the first 3 rows must be removed completely to leave just the header fields and the data.

This is then imported to a database. What I’m looking to do with Xojo, is being able to take this file,
move it to another directory and remove the first 3 rows.

Thanks in advance…

2

So you want to remote control excel or to edit the excel file directly?

For editing excel files directly you can check MBS XL Plugin:
http://www.monkeybreadsoftware.de/xojo/plugin-xls.shtml

3

In this case, will edit the Excel file directly using Xojo.

Can this be done without a plugin?

Thanks…

4

you can of course unzip the xlsx file, edit the xml files inside and try to do the things.

Or you buy the plugin and get this done in 10 minutes.

5

Hi Nelson,

If you have Windows and Excel installed, then this code will work to remove columns:

[code] //Initiates Excel
Dim XL as new ExcelApplication
XL.workbooks.add
XL.visible = true

//Adds data to cells
XL.Range(“A2”).Value = “A2”
XL.Range(“B2”).Value = “B2”
XL.Range(“C2”).Value = “C2”
XL.Range(“D2”).Value = “D2”
XL.Range(“E2”).Value = “E2”

//Clears data in the columns
XL.Columns(“A:C”).Clear[/code]

Happy to help.

6

Oops, you asked to clear rows, not columns. Hear is the code for rows:

[code] //Initiates Excel
Dim XL as new ExcelApplication
XL.workbooks.add
XL.visible = true

//Adds data to cells
XL.Range(“A1”).Value = “A1”
XL.Range(“B2”).Value = “B2”
XL.Range(“C3”).Value = “C3”
XL.Range(“D4”).Value = “D4”
XL.Range(“E5”).Value = “E5”

//Clears data in the rows
XL.Rows(“1:3”).Clear
[/code]

7

Eugene,
IMHO that doesn’t remove the rows but just clears them.

Easiest way to acomplish what the OP wants is to give the the datarange Including the fieldnames a RangeName, e.g. “DBExcel”.
Then open the Excel file with an ODBC connector and then the database is visible with that name and fieldnames.

In this case you don’t have to remove lines and the named range can be anywhere in the Excel model.

8

Hi Andre. Yes, you are right, the example I gave clears the rows and leaves the header files and empty places for the data which is what I believe the OP wanted. I could have misinterpreted this request.

If the Delete command is used then the rows, header files, and empty data fields are removed, which I believe the OP does not want:

XL.Rows("1:3").Delete

I like your suggestion about using an ODBC connector and had not thought about this functionality - well done!

9

Excellent feedback. Thanks

10

be aware this works only on a windows os …

11

The delete command worked perfectly.

Not to ask too much, but does someone knows how to call a document that has been saved?

I have the code of how to remove the columns, but on this scenario, I need the following:

  1. Search for the document (This is for Windows only!)
  2. Open the document.
  3. Remove the columns
  4. Save document in another directory with a different name.

Any help?

12

Not clear if this has been properly answered or not so, here’s my “method”…

Assuming you’ve already opened the Excel file and are referencing the Excel object as oExcel.

oExcel.Rows("1:3").Select oExcel.Selection.Delete Shift:=xlUp

This will completely remove the first 3 rows and everything below will move up.

13

Thanks