copy/past values on an Excel workbook

[mveijndh]

I have data copied to an excel workbook.
In the book I do some pasting of values. These are copied by formulas in another sheet.
To get rid of the calculations I want to copy and Past the values on the same sheet.
That's where things go wrong.

Set sht = excelApp.ActiveWorkbook.Sheets(1)
sht.Activate
sht.Cells.Select
With Selection
.Selection.Copy
.Selection.PasteSpecial Paste:=xlPasteValues
End With

It all goes well up to the sht.Cells.Select. I can see the sheet is selected. I can't copy or past the selection on the same sheet.
How should I do that??

 

[arnelgp]

change this:

With Selection
.Selection.Copy
.Selection.PasteSpecial Paste:=xlPasteValues
End With

to:

With Selection
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

 

[JHB]

change this:

With Selection
.Selection.Copy
.Selection.PasteSpecial Paste:=xlPasteValues
End With

to:

With Selection
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

Selection is unknown in MS-Access, so you also need the reference to the Excel object = sht.

 

[mveijndh]

Is there a way to delete the second worksheet from a workbook??
Thanks beforehand

 

[Cronk]

In any case, you are copying a selection and then pasting it back into the same section. You need to select another range before pasting.

To delete a sheet, if excelApp is your excel object

excelApp .activeworkbook.sheets("YourSheetName").delete

 

[mveijndh]

Hi,

Indeed I'm copying the page over its own, but now without any calculations, just data!!
Just two other questions, This information, where can I find this, is there any source I could find for this to improve my scills?
Second, can I use this to save the current workbook with a known path and workbook name??

 

[Cronk]

To improve your skills
(1) Google search
(2) Record macro steps in Excel
(3) Read these forums, or similar in Excel

You can use workbook.saveas to save to a specific file.

 

[mveijndh]

I must be doing something wrong as the copy past routine is inconsistent. It Always works the first time. The second time it Always crashes with a the message:
Object variable or block variable with not set.
The Excel file is closed 100% sure. I don't know what's going on.
thit is the part that works the one time, but not the next:

Set sht = excelApp.ActiveWorkbook.Sheets(1)
sht.Activate
sht.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Can I use the following routine to write data to an array where I can be 100% sure both title and data are in there on the same array line? I Always have only 1 record selected!

For Each fldHeadings In rst.Fields
excelApp.ActiveCell = fldHeadings.Name
excelApp.ActiveCell.Offset(0, 1).Select
Next


Can I write the array data to a named range on the Spreadsheet??

Kind regards,

 

[JHB]

I must be doing something wrong as the copy past routine is inconsistent. It Always works the first time. The second time it Always crashes with a the message:
Object variable or block variable with not set.
The Excel file is closed 100% sure. I don't know what's going on.
thit is the part that works the one time, but not the next:

Set sht = excelApp.ActiveWorkbook.Sheets(1)
sht.Activate
sht.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Read post #3 again!
Ask if you don't understand it.

 

[mveijndh]

Yes, There is in principle no difference betweeen
With Selection
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

and:

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Why do I get an error message, on both codes by the way:
Object variable or block variable with not set.

 

[JHB]

Did you actually read the line:

Selection is unknown in MS-Access, so you also need the reference to the Excel object = sht.

[COLOR=Red][B]sht.[/B][/COLOR]Selection.Copy
[COLOR=Red][B]sht.[/B][/COLOR]Selection.PasteSpecial Paste:=xlPasteValues

 

[mveijndh]

Oops! I'll hav a look!
But it does not explain why it's running the first time but is generating an error the second time it's running!

 

[JHB]

It is some silly failure in MS-Access!
It shouldn't even run the first time.

 

[mveijndh]

Hi JHB,
I really appreciate your patience with me. Problem is, it still crahes every other time on the past line. The copy line works fine all the time. The past line provides me with error 438 property or methode not supported.
I can't find out what's causing it.

 

[JHB]

Try by setting the excelApp in front of the lines instead of sht.
Else post your database.

 

[mveijndh]

And indeed that solved it!!!
THANKS!

 

[JHB]

You're welcome, good luck.