pastespecial method of range??

F

Fansons1

New Member
Joined
Dec 28, 2011
Messages
3
Hey, I am working on a program with multiple excel files, copying the same cells off each one, and adding the transpose of all of them to a table in access. The programming is in vba in access. However, the pasteSpecial method isn’t working for me, and I’m always getting a “pastespecial method of range class failed”. I tried a lot of things already but nothing seems to work.

So I tried something simpler, simply adding values to an excel document, copying and pasting the transpose two cells over with the pasteSpecial. But it’s STILL not working. When I run the program with the excel program open to see what’s going on, I see the data’s copied, and the cell E1 is selected, but it's not pasting. I can go in and manually press pasteSpecial, transpose. But the coding isn't running this part of the program, and is consistently throwing the "pastespecial method of range class failed" error.

Can someone see what I’m doing wrong with this simple program, and hopefully I can apply it to the more complicated program?

Thanks a million.

Sub Testing ()

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Database2.accdb"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add

objWorkbook.Sheets("Sheet1").Range("A2") = "A"
objWorkbook.Sheets("Sheet1").Range("A3") = "B"
objWorkbook.Sheets("Sheet1").Range("A4") = "C"
objWorkbook.Sheets("Sheet1").Range("B1") = "1"
objWorkbook.Sheets("Sheet1").Range("C1") = "2"
objWorkbook.Sheets("Sheet1").Range("D1") = "3"
objWorkbook.Sheets("Sheet1").Range("B2") = "x"
objWorkbook.Sheets("Sheet1").Range("C2") = "y"
objWorkbook.Sheets("Sheet1").Range("D2") = "z"
objWorkbook.Sheets("Sheet1").Range("B3") = "i"
objWorkbook.Sheets("Sheet1").Range("C3") = "j"
objWorkbook.Sheets("Sheet1").Range("D3") = "k"
objWorkbook.Sheets("Sheet1").Range("B4") = "5"
objWorkbook.Sheets("Sheet1").Range("C4") = "6"
objWorkbook.Sheets("Sheet1").Range("D4") = "7"

objWorkbook.Sheets("Sheet1").Range("A1:D4").Copy
objWorkbook.Sheets("Sheet1").Range("E1").PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=True

End Sub
 
Sort by date Sort by votes
access has no idea what xlFormats and xlNone mean

you'd have to use VBA in excel to see what those values actually are and then use the actual numeric values for them in access
 
Upvote 0
Or set a reference to Excel.
 
Upvote 0
Ah, is that why.. hmm.

Thing is, I'm running this vba sub through access to process many thousand excel files, so the programming must strictly be through access (no way i'm opening that many excel files :eeek: )

So how would you go about setting a reference to excel?
Isn't objWorkbook.Sheets("Sheet1").Range("E1").PasteSpecial Paste telling the excel document what to do?

Thanks.
 
Upvote 0
You are already running Excel and opening workbooks, so I'm not sure what you mean there.

In the Access VBE, choose Tools-References and then check the reference for the Microsoft Excel Object Library.
 
Upvote 0
and I didn't mean run everything through excel
I meant to only use Excel for 10 seconds to find the actual numeric value of xlFormats
I meant open the excel vba code editor, then type
debug.print xlFormats
in the immediate window

then whatever number comes up, use that number in Access instead of the word xlFormats

but setting a reference is a different way of doing it and is good too
 
Upvote 0
aHA!
Rorya, that made all the difference! Thanks so much for the help.

But just for future reference James, I'm still confused on how to get to the so called 'immediate window'.
I looked it up online, and tried the control+G and doing it under the view tab, but i couldn't get either one to bring up something in which i could put in that debug line for.
Thanks.

ALso, how do i mark this thread as SOLVED?
 
Last edited:
Upvote 0
You have to open the VBEditor, then press Ctrl+G (or do View-Immediate window).
 
Upvote 0
If you want to find the Excel VBA constants try the Object Browser (F2).

The Object Browser will also show you how you could declare the constants in your own code.

For example:
Code: 
Const xlFormats = -4122
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Import Excel sheets in all files in a folder, different structure on each sheet
Replies
7
Views
4K
Razorman
R
P
  • Solved
Update values in a transposed range when source is modified in a vba-generated workbook
Replies
7
Views
101
Fuji
F
B
  • Question Question
VBA to delete attachment created on desktop
Replies
4
Views
139
bh24524
B
T
  • Solved
Add a Unique Value to a Specific Range - VBA
Replies
8
Views
223
rpaulson
rpaulson
S
  • Solved
For loop - Cannot paste as values
Replies
5
Views
58
stormseeker75
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top