Fails to copy excel cell sometimes

[tonn333]

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:

expandcollapse popup

#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.

 

[FrancescoDiMuro]

Hi @tonn333, and welcome to the AutoIt forums 
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 

[tonn333]

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.

[FrancescoDiMuro]

1 minute ago, tonn333 said:

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

Post what you've tried with Excel UDF and, if you can, an example with a detailed explanation of what you are trying to do

[tonn333]

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.... 

[Earthshine]

Post your code

[tonn333]

1 minute ago, Earthshine said:

Post your code

My current script is in first post.

[water]

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).

[tonn333]

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)

 

[FrancescoDiMuro]

43 minutes ago, tonn333 said:

_Excel_RangeRead ( $oWorkbook [$vRange = $oExcel.ActiveCell ] )

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

[tonn333]

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)

 

[water]

#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

[tonn333]

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?

[water]

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

[tonn333]

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?

[water]

Just tried your script - works as expected.

[tonn333]

9 minutes ago, water said:

Just tried your script - works as expected.

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

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()

 

[water]

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.

[tonn333]

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?

[water]

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?