VBA in Access to Copy XL columns

[JBG]

Hi experts,

Here is a code snippet that i am using from within an Access 2000 module to copy the end-30 columns of a spreadsheet into the next 30 empty cols:

 objExcelSheet.Range _
            (strExistingBegColHeader & "1" & _
            ":" & strExistingBegColHeader & intNumberOfRows).Copy
            objExcelSheet.Paste Destination:=objExcelSheet.Range(strBegNewColHeader & "1" & 
            ":" & strEndNewColHeader & intNumberOfRows)

The first row, which contains the headings, copies fine, but all of the rest of the cells mysteriously (to me) do not copy the contents of the cells from the source, nor the formatting (which I beleive is both the formulae and the color stuff - all of which I want, but offset of course).

Instead, all the cells are default white and contain "0" and have a sum formala for every cell (which does not exist in the source).

So, I want to , for example, copy columns DEF(which have data, and say, F has a formula that utilizes D and E) to GHI, and have GHI be identical to DEF, same color fomatting, same formula in F, but the formula offset of course to reflect the new column locations.

As usual, what am I missing?

TIA,

Jeff

 

[TonyJollans]

Hi Jeff,

Can't see anything wrong with your code per se, but it doesn't quite appear to do the same as your description. Your source range is a single column (strExistingBegColHeader ) and your destination range is multi-column (from strBegNewColHeader to strEndNewColHeader ).

If that is not the problem, can you post some sample data.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[JBG]

OKee doke, yes, I will be clearer.
Here is sample code that I want to copy 3 columns, B, C and D in their entirety (including formatting and formulae)
to the next 3 columns E, F and G.

WHen the copy is done, I want 6 columns, the first 3 and and last 3 are exact copies of one another, the only difference being that the formulae in the new/copied columns reference the appropriate columns. That is, if D uses C for deriving data, then G will use F to do the same thing.

If the color of column (all the cells) C is red, then the color of all the cells in E are red too.

objExcelSheet.Range ("B1:D20").Copy _          objExcelSheet.Paste _ 
Destination:=objExcelSheet.Range("E1:G20")

Sample data would be(columns before code is run):

B          C         D         E       F      G

5          10         15
10         20         30 
15         30         45
20         40         60

As u can see, column D sums columns C and D , so there is a formula in the D cells.

When the code is done running, I need to see

B          C         D         E           F   G         

5          10         15       5          10   15
10         20         30       10         20   30
15         30         45       15         30   45
20         40         60       20         40   60

Column G will have the same formula as column D but will be using columns E and F rather than B and C.

In essense, I need to copy 3 columns, colors, formatting, and formula, to 3 other columns...

But when I run my code, this is what I get:

B          C         D         E         F    G         

5          10         15       0          0   0
10         20         30       0          0   0
15         30         45       0          0   0
20         40         60       0          0   0

Jeff

 

[oharab]

What I do when manipulating Excel is record a macro from Excel doing what you want it to do, then copy the code to Access making the required adjustments to make it run from Access & to make it fit your variables etc.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[JBG]

Ben, that is exactly what I do too.....

 

[JBG]

It appears I was having some corruption issue with Excel. I have 2 desktops, and I just tried my orignal code on the other machine and it works fine on the second machine. My first machine gives the dreaded "memory could not be read" when I close Excel.

Thanks experts....I appreciate the replies. My a$$ is continually saved by you folks....

Jeff