0

I have a dataframe from R that I need to paste into the first empty row of an open Excel spreadsheet.

I have tried numerous things.

This code throws a "Run-time error '1004': Application-defined or object-defined error". 

Dim NextRow As Range
Set NextRow = Range("B" & Sheets("TC-9").UsedRange.Rows.Count + 1)
Worksheets("TC-9").Range("A" & NextRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I also tried using xlUp to look from the bottom up to find the first empty row.

Cells(Range("C1000000000").End(xlUp).Row + 1, 3).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Community
  • 1
  • 1
Koda
  • 165
  • 5
  • 13
  • 1
    possible duplicate http://stackoverflow.com/questions/5552299/how-to-copy-to-clipboard-using-access-vba – TZubiri Dec 06 '16 at 17:50
  • What is the workflow that you are following: you copy the R code, then you select your open worksheet, right?... Do you have a button on your excel file with a routine or something like that? – Hackerman Dec 06 '16 at 17:50
  • http://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard can you use the R Addin? – Nathan_Sav Dec 06 '16 at 17:50
  • You are correct. I built a routine in R that scraps some data off the web then processes its. At the end it copies the dataframe from R onto the clipboard. I need it to then paste the data into Excel. – Koda Dec 06 '16 at 17:52
  • what's your issue? isn't it pasting anything? or is it pasting from the wrong cell? – user3598756 Dec 06 '16 at 18:03
  • This first code set throws a "Run-time error '1004': Application-defined or object-defined error". – Koda Dec 06 '16 at 18:09
  • R allows you to export dataframes as csv files, files which are easily read by Excel. Why go through the clipboard? – John Coleman Dec 06 '16 at 18:20
  • My boss wants to be able to run the package through a push button on Excel meaning the spreadsheet is already open. To do R to csv would require closing the spreadsheet (which my boss is not to keen on) then reopening. Using the clipboard we can skip the closing process. – Koda Dec 06 '16 at 18:23

4 Answers4

1

Try this

dim lastrow as integer

With Worksheets("TC-9")
    lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    .Range("A" & lastrow + 1).PasteSpecial xlPasteValues, _ 
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End With
Ash Notts
  • 61
  • 3
0

In your PasteSpecial line, you are specifying a Range as Range("A" & NextRow). NextRow has been defined as a Range itself, and concatenating a String with a Range isn't valid.

Change your code as follows:

Dim NextRow As Long
NextRow = Sheets("TC-9").Range("B" & Sheets("TC-9").Rows.Count).End(xlUp).Row + 1
Worksheets("TC-9").Range("A" & NextRow).PasteSpecial

Note: This will copy a single value from the clipboard. If you want multiple values copied to a single row, it will work if they are tab-delimited but not if they are comma-delimited. (It can probably be done, but would require a bit more work.)

In your second piece of code you used Range("C1000000000") but Excel currently has a limitation of 1,048,576 rows.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
0

If I'm understanding, you're trying to paste a value in that's in the Windows clipboard. The problem is that you can't use Paste Values when pasting from Windows, because it's not copying an Excel object.

Rather than using Range.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, try just using PasteSpecial on its own:

Range.PasteSpecial

You could also just use the .Paste method, but you'll need to select your range first:

Range("A1").select
ActiveSheet.Paste

It doesn't matter what method you use to find the Range, just how you paste it. I prefer the former method, but both should work.

Werrf
  • 1,138
  • 6
  • 14
0

Appending into last of document

Set Range2 = ActiveDocument.Content 
 Range2.Collapse Direction:=wdCollapseEnd 
 Range2.Paste

Reference Link Microsoft-Docs

Amit Panasara
  • 600
  • 8
  • 16