joe.afusco
Board Regular
- Joined
- Jun 25, 2010
- Messages
- 80
-
#1
Getting run-time error ‘-2147221040 (800401d0)’:
DataObject:PutInClipboard OpenClipboard Failed
Any workaround on this error?
Macro takes everything currently on the clipboard, parses through it, pulling specific pieces out, then pastes it into excel. Get this error every time.
Excel Facts
Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
migforce
New Member
- Joined
- Feb 27, 2013
- Messages
- 1
-
#2
Getting run-time error ‘-2147221040 (800401d0)’:
DataObject:PutInClipboard OpenClipboard Failed
Any workaround on this error?
Macro takes everything currently on the clipboard, parses through it, pulling specific pieces out, then pastes it into excel. Get this error every time.
Did you ever solve this?
Thanks.
joe.afusco
Board Regular
- Joined
- Jun 25, 2010
- Messages
- 80
-
#3
Did you ever solve this? Thanks.
We do still get this error from time to time. We run the macro that uses open clipboard roughly 15k times a day. Restarting the station helps fix it. When the issue was more consistent, I noticed the guy who wrote the code for us didn’t kill off his variables on completing execution of the code. Maybe try that?
Similar threads
Поскольку это не получало ответа в течение нескольких лет, и SandPiper предложила мне поделиться ответом, я расскажу о том, что я сделал, в качестве работы.
Некоторый контекст — раньше я работал в компании, которая использовала 64-битный Excel 365, и этого никогда не происходило (включая Excel 2019). Однако недавно я присоединился к новой компании, и они используют 32-разрядную версию Excel 2016 — по определенным бизнес-причинам. При этом, если вы используете более позднюю версию, это решение может сработать для вас, но, кроме того, вы также можете попробовать следующее решение.
Так зачем упомянуть вышеупомянутое? Проблема, с которой я столкнулся с 32-битным Excel, заключалась в том, что выделение виртуальной памяти, которое используется Excel для выполнения таких операций с copy и paste, очень ограничено по сравнению с 64-битными версиями. Таким образом, при использовании 32-битного Excel обычно используется эта виртуальная память и, следовательно, возникает ошибка времени выполнения.
Имея это в виду, одним из решений, которые я придумал, было использование метода Application.Wait, который в принципе дает Excel (приложению) достаточно времени, чтобы догнать программу и позволить ей выполнить команду. Я обнаружил, что лучше всего было заключить это в цикл while, чтобы дать несколько попыток в производственной среде. Пожалуйста, посмотрите код ниже — надеюсь, это поможет другим.
Option Explicit
Sub SelectedRangeToImage()
Const maxPasteAttempts As Long = 5
Dim iPaste As Long
Dim tmpChart As Chart, n As Long, shCount As Long, sht As Worksheet, sh As Shape
Dim fileSaveName As Variant, pic As Variant
'Create temporary chart as canvas
Set sht = Selection.Worksheet
Selection.Copy
sht.Pictures.Paste.Select
Set sh = sht.Shapes(sht.Shapes.Count)
Set tmpChart = Charts.Add
tmpChart.ChartArea.Clear
tmpChart.Name = "PicChart" & (Rnd() * 10000)
Set tmpChart = tmpChart.Location(Where:=xlLocationAsObject, Name:=sht.Name)
tmpChart.ChartArea.Width = sh.Width
tmpChart.ChartArea.Height = sh.Height
tmpChart.Parent.Border.LineStyle = 0
'Paste range as image to chart
On Error Resume Next
While Err.Number <> 0
Application.Wait Now() + "00:00:01"
sh.Copy
tmpChart.ChartArea.Select
Application.Wait Now() + "00:00:01"
tmpChart.Paste
' handling while loop
iPaste = iPaste + 1
If iPaste >= maxPasteAttempts Then
MsgBox "Failed to paste - please consult developer.", vbCritical, "Fatal error"
On Error GoTo 0
Exit Sub
End If
Wend
On Error GoTo 0
'Save chart image to file
fileSaveName = Application.GetSaveAsFilename(fileFilter: = "Image (*.jpg), *.jpg")
If fileSaveName <> False Then
tmpChart.Export Filename:=fileSaveName, FilterName: = "jpg"
End If
'Clean up
sht.Cells(1, 1).Activate
sht.ChartObjects(sht.ChartObjects.Count).Delete
sh.Delete
End Sub
|
|
[RESOLVED] vba — unstable code for clipboard
Just an unsupported ascii character like a line break. Your selection covers two or more lines?
Run-time Error -2147221040 (800401d0) — Excel |
|
Getting run-time error ‘-2147221040 (800401d0)’:
DataObject:PutInClipboard OpenClipboard Failed
Any workaround on this error?
Macro takes everything currently on the clipboard, parses through it, pulling specific pieces out, then pastes it into excel. Get this error every time.
Excel VBA Course — From Beginner to Expert
200+ Video Lessons
50+ Hours of Instruction
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
(80% Discount Ends Soon!)
View Course
Similar Topics
How can i solve a debugger error that states «run-time error ‘9’…Subscript out of range.
for a search box code provided by Joe Was
Is there an on error exit sub command. I would like my Macro to just stop running if there is an error instead of an error message popping up. Thanks in advance
Hi all,
I’ve been getting this error on occasion recently.
— It’s a shared document
— There is usually 10-12 people using the file at the same time
I’ve read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it.
Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error?
Thanks!
Is it possible to share a workbook with macros and have the macros work properly. I have one spreadsheet that needs to be shared by many users. We want to all be in the same document at the same time saving changes. The real catch is that the workbook has macros. I used the ‘Share Workbook’ function under tools. At the end of the setup, it told me that the macros would not work properly. The odd thing is that everytime I try to click on one of the macros I get an error, but yet the macro still performs the function. So essentially the macro works, it is just a pain because you have to click ‘end’ everytime on the error screen. Any suggestions?
Alanda
I have a large spreadsheet in Excel 2007. I am converting it to a .csv file to import in to another program. I need to delete all commas from all data.
When I try to replace all commas (with nothing or with another character), I get the error message «The formula you typed contains an error.»
I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error.
I am trying to figure out how to write a formula to figure out production cycle time.
We will be building 8 «widgets» a day.
We will work 10 hours a day.
There is a 20 minute break at 9:20 am.
Production shuts down for lunch 30 minutes for lunch at 12:30.
Production starts at 6:00 am.
Here is what I can do.
Production cycle time = (10*60)-30/8 or 71.25 minutes
If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)
My problem is when I am trying to use a logical «IF’ statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.
This is the formula I am trying to make work. I have the cell the formula is in, formatted with a «mm,ss» format.
=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))
I have included an attachment to help (a picture is worth a thousand words).
Any help will be greatly appreciated.
I have bought a number of books in an attempt to figure this out, and I am still stumped
Happy Day to all,
Can you please help me,
A1= time in
B1= time out
C1= time in
D1= time out
I want to calculate the late and under time,
Office start at 9:am w/30 mins Grace period,
The break time is one hour only, please include over breaktime in calculation.
End of office hours 6:00 pm, strickly no over time
Hi all,
I’ve had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn’t see anything, but if I am repeating prior information I do apologise.
I’ve written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer.
Now, I need to share this macro with some other people, so basically I’ve just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can’t seem to get the macro to run properly.
It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn’t completed properly.
I have checked Macro Security level and that is the same as mine, Tools — Add-Ins is the same, In Visual Basic, Tools — References is the same as mine. It is the same Operating system and the same version of Excel.
I have even signed into this person’s computer as myself (it’s a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.
I’ve googled and searched and tried everything I can think of but I’m no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!
Thanks very much for your time.
Good day… I need an IF Function that will allow me to action a time in a time range:
… If the time 04:16 falls in the time range 04:00 — 04:29, than put a one (1) in the filed x…
… If the time 04:16 doesn’t fall in the time range 04:00 — 04:29, than leave the x fiel empty
Any help is appreciated.
In Excel I have been trying to find an easier way to calculate a time
difference where the times cross midnight. Example:
Start time: 23:50:00
End time: 00:15:00
How would you formulate an equation to determine the duration of time or
differnce between the start and end time?
i have a worksheet with formulas in 15,000 cells; i have set the calculation to maunal so that you have to press F9 to calculate the sheet.
sometimes, it takes 45 seconds to calc the whole sheet, other times, it takes 5 minutes. how do i clear the cache, or what do i have to do so that it calcs 45 seconds each time.
i don’t want the application to think, it would be better if those 15,000 cells can be returned in a fraction-of-a-second. any ideas/tips? thanks.
Hello,
Please help!!!
We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:
From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.
They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKED-OR- choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER
I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.
Thanks..
Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don’t even know what question to ask on this one!?!
Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)?
In previous years I made a column for each size, and simply placed a «1» in the correct column, and had excel just add the 1’s from each column. However, that takes more time and space. I was hoping to streamline it this time around.
Thanks for taking the time to read this post. Any help would be appreciated! Thanks, doug
Hi Guys,
I need help….I am working production scheduling with Start and End time but there is a lunch time and tea time. Is there any formula to calculate Start time to End time with excluding lunch time?
Here is example :
Lunch time :12:00pm to 1:00pm.
Duration Process :6 hours.
Start Time (8:00am)
End time (5:00 pm)
I need a formula to calculate from Start Time 8:00am and what is the End Time? with 1 hour lunch time…
Thanks,
ET
I am attempting to figure out how to change the formula to automatically subtract a 30 minute lunch if the total time for the day exceeds 6 hours worked.
B value Time of day punch in: 9:30 AM
C value =IF(D11=»»,»»,»-«)
D value Time of Day punch out: 6:00 PM
E value =IF(D11=»»,»»,IF(D11
I get the error message «reference is not valid» each time I open my spreadsheet. I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. The problem is, I have to send it to a bunch people.
I have
a sheet with raw data
a sheet with pivot tables
a sheet with a dashboard
and a simple macro
I don’t have any #REF cells either.
Someone has any idea of what could be the problem?
thanks
NA
I found this solution for «drop down list with hyperlink» but it did not work.
Perhaps a better solution is to use a workaround that relies on the HYPERLINK function to refer to whatever is selected in the drop-down list. For instance, if you have your data validation drop-down list in cell A1, then you might put the following formula in cell B1:
=HYPERLINK(A1, «Goto Link»)
The solution directly above provides exactly what I am looking for
in the field where I write the formula, but it fails to hyperlink.
I have created a drop down list and linked each one of them to a
specific worksheet. When I select them individually they link to
appropriate worksheet. But when I select them in the drop down
list I receive the following error when I select the Hyperlink in
cell B1 as directed above.
«Cannot open the specified file»
Any thoughts?
Bob
I have an excel worksheet that adds two other worksheets in a data
triangle. I copied it to create a new data set and used find &
replace to change the worksheet references to the new ones.
The cells still contain the result of the old formula referring to the
previous worksheets. The only way I can get the formula to return the
correct result is to edit (F2) each cell and press enter. Calc now
(F9) does nothing.
I’ve seen this before, but this time, I need to calculate many
thousands of cells and don’t have time for this workaround.
Any ideas?
Thanks.
Don S
I have created a simple macro in this excel spreadsheet. Everything works fine until I sent the attachment to my boss. It does not work and keep showing s pop up error msg.
What could be wrong?
Hello, I am pretty new to excel programming/messing around so I’ll refer to those who are much wiser than I am. I am trying to set up a form for work, in this form I want to have a page with a bunch of cells pre-sized. What I want to do is when I insert a picture from a job site, that when this picture gets inserted, it will automatically resize to fit within the cell. I don’t have time to manually resize dozens of pictures and if I could get this to work it would be awesome! I tried to search for an answer using the search function, but I kept getting a fatal error about allowable memory? Thanks in advance for you help!