Jump to content
Sign in to follow this  
tonn333

Fails to copy excel cell sometimes

Recommended Posts

I'm exporting pictures to png from InDesign and geting the names from Excel.

Problem I have atm is that sometimes it fails to copy. I've tried all I know (which is very little tbh).
Copying from excel is done by my excel macro shortcut ctrl+o. There should be a better way Im sure. Probably with _Excel_RangeCopyPaste... 

Heres my script:

#include <array.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <Misc.au3>

HotKeySet("+1", "_Dowork")
HotKeySet("{ESC}", "_Exit") ;If you press ESC the script will stop

Global $INDD = "*w31.indd @ 75%"
Global $1500_INDD = "*1500x1500px.indd @ 25%"
Global $XLSX = "w31.xlsx"
Opt("WinTitleMatchMode", 2)

While 1
Sleep(100)
WEnd

Func _Exit()
Sleep(100)
Exit
EndFunc ;==> _Exit()

Func _Dowork()

;WinActivate($1500_INDD, "")
Send("^c") ;copy picture in InDesign
;Sleep(100)
Send("^{TAB}") ;Alt+tab to next InDesign document because I don't know how to switch documents any other way. WinActivate doesn't work when it's not the active one 
WinWaitActive($1500_INDD, "")
Send("^!v") ;Paste into frame (unfortunately frame has to be manualy selected beforehand)
Sleep (50)
Send("^+!e") ;Fit picture to frame
WinActivate($XLSX, "")
WinWaitActive($XLSX, "")
$dll = DllOpen("user32.dll")

While NOT _IsPressed("01", $dll) ;wait for mouseclick
   Sleep (10)
WEnd
Sleep (100)
Send("^o") ;copy with my excel macro shortcut (macro just copies :D)
Sleep (100)
DllClose($dll)
WinActivate($1500_INDD, "")
WinWaitActive($1500_INDD, "")
Send("^e") ;export shortcut in InDesign
WinWaitActive("Export", "")
Send("^v") ;paste name copied from excel (when it managed to copy)
Sleep (10)
Send("{ENTER}")
WinWaitActive("Export PNG", "")
Send("{ENTER}")
WinWaitActive($1500_INDD, "")
Send("^{TAB}") ;go back to original InDesign document so I can select another picture to do the same thing with

EndFunc

Thanks in advance for any improvements, tips or helpful comments.

 

Share this post


Link to post
Share on other sites

Hi @tonn333, and welcome to the AutoIt forums :welcome:
You can improve your code defintiely a ton using Excel UDF, especially the function _Excel_RangeCopyPaste() or eventually _Excel_RangeRead().
You can find them in the Help file, with samples too :)


Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Problem is I don't know how. I've tried. 

One thing that makes it harder than it already is is that depending on the picture I either have to name its product name or EAN code. Would be cool to automate this but I don't know how.

Share this post


Link to post
Share on other sites

I don't have it saved unfortunately. But I couldn't even figure out how to read active cell in nonspecific already open excel workbook.

So I moved on with what atleast works according to my needs.

I try to give you detailed explanation: What I need to do is export pngs of all the product pictures in my InDesign file. It's a product catalog printed weekly. PNGs are needed for web. If its one product then I need to name it its EAN number in column E. If its multiple products then I need to name it its product name in column Q. Sometimes column Q (product/offer name) contains illegal characters like / which can't be included in PNG file name. Column E is either empty or 1 when it's multiple products. 

More automation is great and I can work to achieve that if I could get started. But my main goal at the moment is getting something that works flawlessly.... 

Share this post


Link to post
Share on other sites
28 minutes ago, tonn333 said:

But I couldn't even figure out how to read active cell in nonspecific already open excel workbook.

Let's do it step by step.
To connect to an already open WorkBook you need to use function _Excel_BookAttach.
To read the active cell use _Excel_RangeRead and pass $oExcel.ActiveCell ass parameter $vRange ($oExcel is the Excel application object as returned by _Excel_Open).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
On 7/24/2019 at 1:16 PM, water said:

Let's do it step by step.
To connect to an already open WorkBook you need to use function _Excel_BookAttach.
To read the active cell use _Excel_RangeRead and pass $oExcel.ActiveCell ass parameter $vRange ($oExcel is the Excel application object as returned by _Excel_Open).

Thanks for trying to help. Unfortunately you'd have to dumb it way down for me to be able to understand.

This is what I have trying to test it out. (copy pastes from examples)

#include <Excel.au3>
#include <MsgBoxConstants.au3>

$sMode = "filename"
Local $sWorkbook = "w32.xlsx"
$oWorkbook = _Excel_BookAttach($sWorkbook, $sMode)
Local $oExcel = _Excel_Open()
Local $vRange
Local $active = _Excel_RangeRead ( $oWorkbook [$vRange = $oExcel.ActiveCell ] )

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Search by 'filename':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of active cell: " & $active)

 

Share this post


Link to post
Share on other sites
3 minutes ago, FrancescoDiMuro said:

The square brackets are meant to indicate that the parameter inside those is optional, so, in this case, removing them will remove the error :)

Thanks but it still doesn't work... and it opens new excel windows... When I close that window it also closes my other excel window.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

$sMode = "filename"
Local $sWorkbook = "w32.xlsx"
$oWorkbook = _Excel_BookAttach($sWorkbook, $sMode)
Local $oExcel = _Excel_Open()
Local $vRange
Local $active = _Excel_RangeRead ( $oWorkbook, $vRange = $oExcel.ActiveCell )

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Search by 'filename':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of active cell: " & $active)

 

Share this post


Link to post
Share on other sites
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $sWorkbook = "w32.xlsx", $sMode = "filename", $vRange
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook, $sMode)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $sActiveCell = _Excel_RangeRead($oWorkbook,Default, $oExcel.ActiveCell)

You first have to create a connection to the Excel application by using _Excel_Open.
You then need to read the help file and understand how to call a function by providing the needed parameters (in your example the parameters for _Excel_RangeRead are wrong).
Then you need to check for errors and have a look at the help file (again) to understand what the numeric error codes tell you :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
27 minutes ago, water said:
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $sWorkbook = "w32.xlsx", $sMode = "filename", $vRange
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook, $sMode)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $sActiveCell = _Excel_RangeRead($oWorkbook,Default, $oExcel.ActiveCell)

You first have to create a connection to the Excel application by using _Excel_Open.
You then need to read the help file and understand how to call a function by providing the needed parameters (in your example the parameters for _Excel_RangeRead are wrong).
Then you need to check for errors and have a look at the help file (again) to understand what the numeric error codes tell you :)

Starting to understand now. Thank you.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

HotKeySet("+1", "_Dowork")
HotKeySet("{ESC}", "_Exit") ;If you press ESC the script will stop

Local $sWorkbook = "w32.xlsx", $sMode = "filename", $vRange
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook, $sMode)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

While 1
Sleep(100)
WEnd

Func _Dowork()
Local $sActiveCell = _Excel_RangeRead($oWorkbook,Default,$oExcel.ActiveCell)
MsgBox($MB_SYSTEMMODAL, "Message box", "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of active cell: " & $sActiveCell)
EndFunc

Func _Exit()
Sleep(100)
Exit
EndFunc ;==> _Exit()

Why does value of active cell always read 0?

Share this post


Link to post
Share on other sites

Hard to tell:
Whave you selected a single cell in the w32.xlsx workbook? Or a range of cells?
Is Excel the active window?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
10 minutes ago, water said:

Whave you selected a single cell in the w32.xlsx workbook? Or a range of cells?
Is Excel the active window?

I tried both single and range... both return 0

yes Excel is the active window as I'm pressing the hotkey to launch function _Dowork

Is there something I could do to pinpoint the problem?

Share this post


Link to post
Share on other sites

Just tried your script - works as expected.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
9 minutes ago, water said:

Just tried your script - works as expected.

yeah me too... I just don't expect it to work :D

Bizarre, I didn't change anything, but now it doesn't even attach. Gives error.
@error = 1

@extended = -2147352570

What am I supposed to do with those?

#include <Excel.au3>
#include <MsgBoxConstants.au3>

HotKeySet("+1", "_Dowork")
HotKeySet("{ESC}", "_Exit") ;If you press ESC the script will stop

Local $sWorkbook = "w32.xlsx", $sMode = "filename", $vRange
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook, $sMode)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

While 1
Sleep(100)
WEnd

Func _Dowork()
Local $sActiveCell = _Excel_RangeRead($oWorkbook,Default,$oExcel.ActiveCell)
MsgBox($MB_SYSTEMMODAL, "Message box", "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of active cell: " & $sActiveCell)
EndFunc

Func _Exit()
Sleep(100)
Exit
EndFunc ;==> _Exit()

 

Share this post


Link to post
Share on other sites

The error stands for: HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME)
You get this error when Excel is not installed on the computer or not available for the current user.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites
43 minutes ago, water said:

The error stands for: HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME)
You get this error when Excel is not installed on the computer or not available for the current user.

I figured out why it didn't attach. I ran SciTE as admin. How does one find the meaning of error codes. I couldn't find it in help file.

Now that it attaches again the problem remains for me: the active cell reads 0. 

#include <Excel.au3>
#include <MsgBoxConstants.au3>

HotKeySet("+1", "_Dowork")
HotKeySet("{ESC}", "_Exit") ;If you press ESC the script will stop

Local $sWorkbook = "w32.xlsx", $sMode = "filename", $vRange
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookAttach($sWorkbook, $sMode)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

While 1
Sleep(100)
WEnd

Func _Dowork()
Local $sActiveCell = _Excel_RangeRead($oWorkbook,Default,$oExcel.ActiveCell)
MsgBox($MB_SYSTEMMODAL, "Message box", "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of active cell: " & $sActiveCell)
EndFunc

Func _Exit()
Sleep(100)
Exit
EndFunc ;==> _Exit()

This script works for you? Displays a message with value of active cell something other than 0?

Share this post


Link to post
Share on other sites

You do not find this error codes in the help file because they are not AutoIt or SciTE related. This are pure Windows error codes.

  • Start Windows calculator
  • Switch to programmer mode
  • copy the decimal error code (e.g. -2147352570) to the calculator and retrieve the hex representation
  • Google for "HRESULT 0x80020006" to get the error description

I copied your script, modified the workbook name, selected a cell in Excel and then used Ctrl+1 to retrieve the value of the selected cell.

Which cell did you select (e.g. "A1) and which value do you expect? is it a plain value or a formula in the cell?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...