Dealing with Excel files, columns and cells

[drimades]

-----------------------------

Hello!

I have the following questions:

1. How can I open an Excel file from VB to work in it and then save and close it?

2. Can I give a name to a column, to a cell, or even to a sheet so to identify the positions regardless of the language and to find the right cells even if someone adds rows or columns in an existing sheet?

Thank you my friends!

-----------------------------

 

[01Patrik]

1.

application.screenupdate = false
workbooks.open (yourfilename.xls)

your work in VB

activeworkbook.close savechanges:=true
application.screenupdate = true

2.

Yes you can:

First select the range you wnat to name (entire sheet, one ore more columns or a few cells doesn't matter).

Second, use the name field above column A (the field which shows you the address to the cell currently activated within selection). In that field, enter the name you wnat to assign to the range!

As an alternative, use Insert | Name | Define. Here you can also edit previously entered named ranges.

Note that named ranges can be used inside functions / formulae instead of writing the range address.

Good luck!



help for details.

// Patrik

 

[drimades]

---------------------------------------

What if I need to add a row after the last not empty row and write data to it?

---------------------------------------

 

[xlbo]

Then you need to go to the FAQ section and tread the 2 FAQs on how to find the last row of data in a spreadsheet

You will NOT need to add a row. In fact, that is technically impossible within excel as it has, by default 65536 rows - and can have no more and no less - you can HIDE rows but they are still there and as such, you cannot ADD rows

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[drimades]

-----------------------------------------

Ok! Maybe I used a wrong expression.

The situation is like this (for example):

Row 1: 23 45 12 23
Row 2: 12 35 22 33


Total: 1200

I just want to shift the total every time I want to add data.

-----------------------------------------

 

[luceze]

Going by your example you could try this:

Range("e65536").End(xlUp).ClearContents
Range("A65536").End(xlUp)(2) = 5
Range("B65536").End(xlUp)(2) = 10
Range("C65536").End(xlUp)(2) = 15
Range("D65536").End(xlUp)(2) = 20
Range("E" & Range("B65536").End(xlUp).Row + 3).Formula _
    = "=SUM(A2:D" & Range("D65536").End(xlUp).Row & ")"

You will need to change your cell references accordingly.

Note: that this example is oversimplified. If there is data below your table then the above code will fail.


HTH,
Eric

 

[drimades]

-------------------------------------

So I can't simulate the "insert row" command of Excel from VB?

-------------------------------------

 

[xlbo]

Top tip
1: Try the macro recorder
2: Try the help files

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[drimades]

--------------------------

Solved like this:

.Range("pivot").EntireRow.Insert (shift)

where "pivot" is defined as the first empty row.

Ok! Thank you

--------------------------