AHK+Excel, paste image each X rows Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
michalb93
Posts: 46
Joined: 29 Sep 2022, 04:35

AHK+Excel, paste image each X rows

Post by michalb93 » 29 Nov 2022, 06:41

I tried to make little project. You have folder X, in this folder are Y images (5 or 100, doesnt matter). With ahk script it should paste image and add caption/text under this. I can't make it work, cuz I have problem with variables. Let's say I have 'A1' cell. I tried to split it to something like:
cell_val := 1
OR
cell_val = 1
So I could have xlRng := xlApp.Range("A%cell_val%") as xlRng := xlApp.Range("A1") . But thousands of errors occured while I tried... I just wanted to add new image from A17 cell, next one at A33, so image every 16 rows. Similar with text. First text at A15, next text A31. Now I wonder if IS IT EVEN POSSIBLE to manipulate such excel methods/variables etc? And what I tried is impossible? Code below is working fine, adds image from given folder and their filenames under... but it just adds one picture ON another (stacking them) so its not finished.

Code: Select all

#Include XL.ahk

FileSelectFolder, WhichFolder
XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add
XL.Visible := true
WinMaximize, ahk_exe excel.exe
Sheet := XL.ActiveSheet
ComObjConnect(Sheet, Worksheet_Events)
Sheet.Columns("A").ColumnWidth := 37


Loop Files, %WhichFolder%\*.*, R  
{
FilePath := A_LoopFileFullPath
SplitPath, A_LoopFileFullPath, namefile

xlApp := ComObjActive("Excel.Application") 
xlRng := xlApp.Range("A1")                 ;paste first image at A1
xlApp.ScreenUpdating := false
xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FilePath, false, true, xlRng.Left, xlRng.Top, -1, -1)
xlShape.LockAspectRatio := true
xlShape.Width := 200
xlApp.ScreenUpdating := true
Xl.Range("A15").Value := namefile         ;add file-name text under image
}




User avatar
mikeyww
Posts: 26601
Joined: 09 Sep 2014, 18:38

Re: AHK+Excel, paste image each X rows

Post by mikeyww » 29 Nov 2022, 06:59

Code: Select all

Loop, 5 {
 picRow := 16 * A_Index + 1
 txtRow := 16 * A_Index - 1
 MsgBox, %picRow% %txtRow%
}

michalb93
Posts: 46
Joined: 29 Sep 2022, 04:35

Re: AHK+Excel, paste image each X rows

Post by michalb93 » 29 Nov 2022, 07:29

mikeyww wrote:
29 Nov 2022, 06:59

Code: Select all

Loop, 5 {
 picRow := 16 * A_Index + 1
 txtRow := 16 * A_Index - 1
 MsgBox, %picRow% %txtRow%
}
I have totally no idea how to use it, still gives same error. Am I supposed to make loop within loop? Use these variables in xlRng := xlApp.Range("A1") ? Below is error when you try to change xlApp.Range("A1") to xlApp.Range(%picRow%) or xlApp.Range("%picRow") or xlApp.Range(picRow):
Image


Code: Select all

#Include XL.ahk

FileSelectFolder, WhichFolder
XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add
XL.Visible := true
WinMaximize, ahk_exe excel.exe
Sheet := XL.ActiveSheet
ComObjConnect(Sheet, Worksheet_Events)
Sheet.Columns("A").ColumnWidth := 37




Loop {
Loop Files, %WhichFolder%\*.*, R  
{
	picRow := 16 * A_Index + 1
 	txtRow := 16 * A_Index - 1
	FilePath := A_LoopFileFullPath
	SplitPath, A_LoopFileFullPath, namefile

	xlApp := ComObjActive("Excel.Application") 
	xlRng := xlApp.Range(%picRow%)                 ;paste first image at A1
	xlApp.ScreenUpdating := false
	xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FilePath, false, true, xlRng.Left, xlRng.Top, -1, -1)
	xlShape.LockAspectRatio := true
	xlShape.Width := 200
	xlApp.ScreenUpdating := true
	Xl.Range("A15").Value := namefile         ;add file-name text under image
}
}




User avatar
mikeyww
Posts: 26601
Joined: 09 Sep 2014, 18:38

Re: AHK+Excel, paste image each X rows  Topic is solved

Post by mikeyww » 29 Nov 2022, 07:58

Use an expression.
Variable names in an expression are not enclosed in percent signs (except for pseudo-arrays and other double references).

Code: Select all

Loop, Files, %WhichFolder%\*.*, R
{
 xlApp  := ComObjActive("Excel.Application")
 picRow := 16 * A_Index + 1 ; 17, 33, 49,...
 txtRow := 16 * A_Index - 1 ; 15, 31, 47,...
 xlRng  := xlApp.Range("A" picRow)
 ...
EDIT: you may be able to put line 3 here before the loop. I did not test the script.

michalb93
Posts: 46
Joined: 29 Sep 2022, 04:35

Re: AHK+Excel, paste image each X rows

Post by michalb93 » 29 Nov 2022, 08:21

mikeyww wrote:
29 Nov 2022, 07:58
Use an expression.
Variable names in an expression are not enclosed in percent signs (except for pseudo-arrays and other double references).

Code: Select all

Loop, Files, %WhichFolder%\*.*, R
{
 xlApp  := ComObjActive("Excel.Application")
 picRow := 16 * A_Index + 1 ; 17, 33, 49,...
 txtRow := 16 * A_Index - 1 ; 15, 31, 47,...
 xlRng  := xlApp.Range("A" picRow)
 ...
I keep forgetting about expressions...
Btw how to change it so first image is pasted into A1 instead of A17? I managed to paste first image and text in right place, but with errors :/

Code: Select all

#Include XL.ahk

FileSelectFolder, WhichFolder
XL := ComObjCreate("Excel.Application")
XL.WorkBooks.Add
XL.Visible := true
WinMaximize, ahk_exe excel.exe
Sheet := XL.ActiveSheet
ComObjConnect(Sheet, Worksheet_Events)
Sheet.Columns("A").ColumnWidth := 37

x := ComObjCreate("Scripting.FileSystemObject").GetFolder(WhichFolder).Files.Count


Loop, 12{
Loop Files, %WhichFolder%\*.*, R  
{
	FilePath := A_LoopFileFullPath
	SplitPath, A_LoopFileFullPath, namefile
	pic += -17
	txt += -17

	xlApp := ComObjActive("Excel.Application")
	picRow := pic + 18
 	txtRow := txt + 32  
	xlRng := xlApp.Range("A" picRow)                 ;paste first image at A1
	xlApp.ScreenUpdating := false
	xlShape := xlApp.ActiveSheet.Shapes.AddPicture(FilePath, false, true, xlRng.Left, xlRng.Top, -1, -1)
	xlShape.LockAspectRatio := true
	xlShape.Width := 200
	xlApp.ScreenUpdating := true
	Xl.Range("A" txtRow).Value := namefile         ;add file-name text under image
}
}




User avatar
mikeyww
Posts: 26601
Joined: 09 Sep 2014, 18:38

Re: AHK+Excel, paste image each X rows

Post by mikeyww » 29 Nov 2022, 09:19

You could always subtract one from the index before multiplying by 16, right?

michalb93
Posts: 46
Joined: 29 Sep 2022, 04:35

Re: AHK+Excel, paste image each X rows

Post by michalb93 » 29 Nov 2022, 09:32

mikeyww wrote:
29 Nov 2022, 09:19
You could always subtract one from the index before multiplying by 16, right?
It did work indeed, thanks! So 'A_Index = 1' all the time?

User avatar
mikeyww
Posts: 26601
Joined: 09 Sep 2014, 18:38

Re: AHK+Excel, paste image each X rows

Post by mikeyww » 29 Nov 2022, 09:35

The AutoHotkey documentation is a good source of information like this.

Loop:
The built-in variable A_Index contains the number of the current loop iteration. It contains 1 the first time the loop's body is executed. For the second time, it contains 2; and so on. If an inner loop is enclosed by an outer loop, the inner loop takes precedence. A_Index works inside all types of loops, including file-loops and registry-loops; but A_Index contains 0 outside of a loop.

Post Reply

Return to “Ask for Help (v1)”