Excel loop through columns with extra macro functions

User-to-User discussion about the PhraseExpress macro programming.
Post Reply
mh348
Posts: 13
Joined: 29 Aug 18, 10:30

Excel loop through columns with extra macro functions

Post by mh348 » 27 Jan 19, 11:01

Hi, I have an excel sheet with a list of filenames in each row.

I then created a macro that reads each row and 'adds' a {#ENTER}{#SPACE} function at the end of the row before proceeding to the next row. I currently have it without any loops, but the code is very messy and difficult to edit if I need to make changes, also for each additional row I want to read, I have to add an entire line to the code. Or if I want to read a different column I have to edit multiple values which is time-consuming.

Is there a way to have just a single line (or 2) and just loop through it while incrementing the row number?

My Current Code:

Code: Select all

{#xls -col 2 -row 1 -endcol 2 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 2 -endcol 2 -endrow 2 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 3 -endcol 2 -endrow 3 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 4 -endcol 2 -endrow 4 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 5 -endcol 2 -endrow 5 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 6 -endcol 2 -endrow 6 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 7 -endcol 2 -endrow 7 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 8 -endcol 2 -endrow 8 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 9 -endcol 2 -endrow 9 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 10 -endcol 2 -endrow 10 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 11 -endcol 2 -endrow 11 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 12 -endcol 2 -endrow 12 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 13 -endcol 2 -endrow 13 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 14 -endcol 2 -endrow 14 -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 15 -endcol 2 -endrow 15 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 16 -endcol 2 -endrow 16 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 17 -endcol 2 -endrow 17 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 18 -endcol 2 -endrow 18 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 19 -endcol 2 -endrow 19 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 20 -endcol 2 -endrow 20 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 21 -endcol 2 -endrow 21 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 22 -endcol 2 -endrow 22 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 23 -endcol 2 -endrow 23 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 23 -endcol 2 -endrow 24 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 25 -endcol 2 -endrow 25 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 26 -endcol 2 -endrow 26 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 27 -endcol 2 -endrow 27 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 28 -endcol 2 -endrow 28 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 29 -endcol 2 -endrow 29 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 30 -endcol 2 -endrow 30 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}{#SPACE}{#xls -col 2 -row 31 -endcol 2 -endrow 31 -file
C:\Users\User\Documents\Friday_sync.xlsx -file C:\Users\User\Documents\Friday_sync.xlsx -colseparator , }{#ENTER}
Sample data from the excel sheet (col2)
2019012512????_backup.mp3
2019020112????_backup.mp3
2019020812????_backup.mp3
2019021512????_backup.mp3
2019022212????_backup.mp3
2019030112????_backup.mp3
2019030812????_backup.mp3
2019031512????_backup.mp3
2019032212????_backup.mp3
2019032912????_backup.mp3

Peter Stotz
PhraseExpress Expert
Posts: 923
Joined: 27 Feb 08, 15:07

Post by Peter Stotz » 27 Jan 19, 21:54

@mh348
Have a look at this suggestion:

Code: Select all

{#settemp -name <file_spec> -content C:\Users\User\Documents\Friday_sync.xlsx}(*
*){#settemp -name <number_of_rows> -content 30}(*
*){#settemp -name <col> -content 2}(*
*){#settemp -name <row> -content 0}(*
*){#loop {#settemp -name <row> -content {#calc {#gettemp <row>}+1 -round 0}}(* 
*){#xls -col {#gettemp <col>} -row {#gettemp <row>} -endcol {#gettemp <col>} -file {#gettemp <file_spec>} -colseparator , }
 -count {#gettemp <number_of_rows>}}
Please note:
• After {#xls ... ... } the Enter key is pressed.

• Char strings for easy adjustment (here with blue font):
{#settemp -name <file_spec> -content C:\Users\User\Documents\Friday_sync.xlsx}(*
*){#settemp -name <number_of_rows> -content 30}(*
*){#settemp -name <col> -content 2}(*
*){#settemp -name <row> -content 0}(* ← this is (starting row minus 1)
*) . . .
You could also use an input form for this purpose . . .

• this are just 'comments' for better readability:
(*
*)

Peter
Windows 10 - PhraseExpress v14.0.118i - Beta

Post Reply