Format Excel Cell

Snickers76 · Post by **Snickers76** » Thu May 29, 2008 5:15 pm

I have a M Scheduler script that reads a text file and combines specific lines into one variable. When the script has gathered all of the lines it needs, it writes it to excel.

My text file looks like this:

Code: Select all

#0004: Job description 01/19/08
Step 1: do this 01/22/08
Step 2: do this after 01/24/08
Step 3: do this after after 01/29/08
#0017: Job description 02/19/08
Step 1: do this 02/22/08
Step 2: do this after 02/24/08
Step 3: do this after after 02/29/08
#0003: Job description 03/19/08
Step 1: do this 03/22/08
Step 2: do this after 03/24/08
Step 3: do this after after 03/29/08

My code works like this:

Code: Select all

FIND specific job number and write it to var_1
FIND next line that doesn't contain include the # character as the first character of the line and combine that line with var_1
QUIT combining lines once you have found the # character

When my code finally enters the value into excel, it obviously looks like this:

Code: Select all

#0017: Job description 02/19/08 Step 1: do this 02/22/08 Step 2: do this after 02/24/08 Step 3: do this after after 02/29/08

But I want it to look like this:

Code: Select all

#0017: Job description 02/19/08
Step 1: do this 02/22/08
Step 2: do this after 02/24/08
Step 3: do this after after 02/29/08

When using excel, you can just press ALT-ENTER to place a soft return into the cell.
How can I make macro scheduler add values to excel's cells using a soft return after each line?

Bob Hansen · Post by **Bob Hansen** » Thu May 29, 2008 5:21 pm

When creating the variable you could add a Line Feed %LF% at the end of each "line".

Snickers76 · Post by **Snickers76** » Thu May 29, 2008 5:29 pm

LF causes only the first found line to be written to excel. All subsequent lines are omitted somehow. I also tried CRLF and CR when DDEPoke>Excel. None allow me to simulate an ALT-ENTER press within Excel.

Code: Select all

//subStoreStatus------------------------------------subStoreStatus
SRT>subStoreStatus
ReadFile>%NPFilepath%,inFile
Separate>inFile,CRLF,lines
Let>k=%JNSLine%-1
Let>FLine=lines_%JNSLine%
Repeat>k
Let>k=k+1
Let>this_line=lines_%k%
Position>#,this_line,1,p
If>p=1
  //found line starting with #,
  If>combined</output>Excel,%ExcelFilepath%:Progress Board,R%RowCount%C2,%FLine%%LF%%combined%
	DDEPoke>Excel,%ExcelFilepath%:Progress Board,R%RowCount%C7,%date%
	END>subStoreStatus
   Endif
	//reset combined
	Let>combined=
Else
  Let>combined=%combined%%LF%%this_line%
Endif
Until>k=lines_count
END>subStoreStatus

Bob Hansen · Post by **Bob Hansen** » Fri May 30, 2008 3:42 am

Just a quick thought without analyzing the DDEPoke stuff ....(The If>combined line looks a bit confusing to me right now) .....

Can you ad a string of 20 spaces vs. the %LF%, and format the cell RxC2 to do word wrapping?

---------------------------
It looks like you sending each line to Excel as it is read. How about concatenating it to a variable until all the lines are complete, then just sending the single concatenated variable with one DDEPoke command? That variable would include the necessary %LF% or long space strings.

Post by **Marcus Tettmar** » Fri May 30, 2008 8:10 am

The only way I have found to send multiple lines to Excel with DDEPoke is to send the data as a formula:

DDEPoke>Excel,Book1,R1C1,="Line1" & Char(10) & "Line2"

So your code would need to change to:

Let>combined="%combined%" & char(10) & "%this_line%"

Then when you send it:

DDEPoke>Excel,%ExcelFilepath%:Progress Board,R%RowCount%C7,=%combined%

But as Bob said, there is something else wrong with your script. Partly perhaps because of the way it has been posed. But I see two End>subStoreStatus lines.

Snickers · Post by **Snickers** » Fri May 30, 2008 1:31 pm

I have tried your suggestion a few times. I have placed the quotation marks in different areas, I can't get it to post to Excel correctly. The code below works great;however, it posts to my excel cell as one long sentence.

I have two END>subStoreStatus because after the job number data has been posted, i want to end this subroutine and grab the new job number from excel and do another search that only correlates with the new job number. I'll send you the entire code if you want to look at it....but I know how tedious that can get. I do comment my codes well though.

My entire code works like this:
1. Determine how many excel rows have data written to them
2. grab R2C1 job number
3. search for job# in text file
4. send the entire job status data to excel
5. repeat process for all job numbers

This is a snippet of the subfunction that handles writing to excel.
[code]
//subStoreStatus------------------------------------subStoreStatus
SRT>subStoreStatus
///Find due date in text file
ReadLn>%NPFilepath%,%JNSLine%,DueDate
Length>%DueDate%,len
Let>len=%len%-8
Midstr>%DueDate%,%len%,8,LastEight
Repeat>LastEightGood
MidStr>%LastEight%,1,1,DateClean
If>{(%DateClean%=0) or (%DateClean%=1) or (%DateClean%=2) or (%DateClean%=3) or (%DateClean%=4) or (%DateClean%=5) or (%DateClean%=6) or (%DateClean%=7) or (%DateClean%=8) or (%DateClean%=9)}
Let>LastEightGood=1
Let>LastEight=LastEight
else
Length>%LastEight%,LElen
Midstr>%LastEight%,2,%LElen%,LastEight
endif
Until>LastEightGood,1
Let>LastEightGood=0
ReadFile>%NPFilepath%,inFile
Separate>inFile,CRLF,lines
Let>k=%JNSLine%-1
Let>FLine=lines_%JNSLine%
Repeat>k
Let>k=k+1
Let>this_line=lines_%k%
Position>#,this_line,1,p
If>p=1
//found line starting with #,indicating the beginning of a different Job Number
If>combined
//get status for jobnumber
DDERequest>Excel,%ExcelFilepath%,R%RowCount%C3,curStatus,60
//remove the CRLF that Excel adds
StringReplace>curStatus,CRLF,,curStatus
//combine the first line of status to the "combined" lines
Let>combined=%FLine% %combined%
//If the current excel status for Job number does not match new data, then write the new data to the excel
If>%curStatus%%combined%
//output combined to Excel
//Mark as New data
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R%RowCount%C9,N
//output due date
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R%RowCount%C2,%LastEight%
//output status info
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R%RowCount%C3,%combined%
//output updated date
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R%RowCount%C8,%date%
END>subStoreStatus
Else
//reset combined
Let>combined=
//Remove new data mark
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R%RowCount%C9,%blank%
END>subStoreStatus
EndIF
Endif
//reset combined
Let>combined=
Else
//if p=0 this indicates i am still within the wanted job number data
//combine that line with any existing lines
Let>combined=%combined%%this_line%
Endif
Until>k=lines_count
END>subStoreStatus
[/code]

This is the main program calling subroutines:
(one of my subroutines determines when the script has completed)
[code]
//declare variables
GoSub>subVarDeclare
ASK>Would you like to sync data between Progress Board and Master Schedule?,sync
If>sync=NO
MDL>PB_Update will now CLOSE.
goto>CLOSEAPP
EndIF
//clean textfile for updating
GoSub>subCleanTxtFile
//declare variables post txt file cleaning
GoSub>subVarDeclare
//check to see if excel file is open
GoSub>subGetExcel
//Calculate total Job #s as written in Excel
//Define the FinalRow
GoSub>subCalcFR
//Loop until all updates are comlete
/////////////////////////////////////////////////////////////////
Label>UpdatePages
//Put to clipboard current JobNumber
GoSub>subGetJN
//Search textfile for starting line of stored job number
GoSub>subFindJNSLine
//Store the Status Information into variable using JNSLine
GoSub>subStoreStatus
Goto>UpdatePages
[/code]

Snickers · Post by **Snickers** » Fri May 30, 2008 1:56 pm

Bob Hansen wrote:Just a quick thought without analyzing the DDEPoke stuff ....(The If>combined line looks a bit confusing to me right now) .....

Can you ad a string of 20 spaces vs. the %LF%, and format the cell RxC2 to do word wrapping?

---------------------------
It looks like you sending each line to Excel as it is read. How about concatenating it to a variable until all the lines are complete, then just sending the single concatenated variable with one DDEPoke command? That variable would include the necessary %LF% or long space strings.

Right now I have it combine each line until if finds another beginning line. Then it compares what has already been stored in the excel status with the newly combined variable. If the new variable is not the same as the excel variable, this indicates it is a new status and it will then write the combined data into excel and also write todays date and mark another cell to indicate the data is new.

Post by **Marcus Tettmar** » Fri May 30, 2008 3:47 pm

Well, this works for me no problem:

DDEPoke>Excel,Book1,R1C1,="Line1" & Char(10) & "Line2"

I therefore see no reason why that method can't be used in your script. I think the problem is therefore somewhere else in your code.

Snickers · Post by **Snickers** » Fri May 30, 2008 4:49 pm

I don't know if you read my code above, but you recommended I use the POSITION function to gather my lines from text.

Your suggested writing to EXcel method does not work with your suggested POSITION function for acquiring the text lines.

mtettmar wrote:DDEPoke>Excel,Book1,R1C1,="Line1" & Char(10) & "Line2"

The above method for writing to excel would require a different variable for each line I gather from my text file.

Your code functions as follows:
read line, store to var1
read line, store as var2
read line, store as var3
read line, store as var4
write to excel, "var1" & Char(10) & "var2" & Char(10) & "var3" & Char(10) & "var4"

My code--the code you initially suggested--functions as follows:
read line, store as var1
read line, combine line with var1, name it var1
read line, combine line with var1, name it var1
read line, combine line with var1, name it var1
wite var1 to excel

POSITION you suggested:
http://www.mjtnet.com/usergroup/viewtopic.php?t=4706

May not make a difference but i am using excel 2003

Is it possible to write to an excel cell. and then write to it again without deleting the previous data in that cell?

If not, then I need to find some way to start at a KNOWN line number; this entire line is already stored as %FLine% variable.

see if the next line begins with an # symbol, if it doesn't then I need to store that line into a variable.

see if the next line begins with an # symbol, if it doesn't then I need to store that line into a variable.

look at the next line and if it begins with a # symbol I need to write %Fline% & Char(10) & %var1% & Char(10) & %var3% to excel cell.

without knowing how many possible lines there could be, how can I be sure that I will write them all to excel with char(10) seperating them?

Snickers · Post by **Snickers** » Tue Jun 03, 2008 2:17 pm

Alright Marcus,

I have re-written the entire portion of the script that writes to excel using your suggestion:

[code]
DDEPoke>Excel,Book1,R1C1,="Line1" & Char(10) & "Line2"
[/code]

You use the words Line1 and Line2 in your suggestion.
Are these variables?

If not, is it possible to use variables in your example?

Here is my new method for writing to excel:

[code]
//if line is beginning of next job number
If>{((%p%=1) or (%k%=%lines_count%)) and (%j%=1)}
//output status info
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R1C%testcellnumber%,"%FLine% & Char(10) & %wantedline_1%"
EndIf
If>{((%p%=1) or (%k%=%lines_count%)) and (%j%=2)}
//output status info
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R1C%testcellnumber%,"%FLine% & Char(10) & %wantedline_1% & Char(10) & %wantedline_2%"
EndIf
If>{((%p%=1) or (%k%=%lines_count%)) and (%j%=3)}
//output status info
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R1C%testcellnumber%,"%FLine% & Char(10) & %wantedline_1% & Char(10) & %wantedline_2% & Char(10) & %wantedline_3%"
EndIf
If>{((%p%=1) or (%k%=%lines_count%)) and (%j%=4)}
//output status info
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R1C%testcellnumber%,"%FLine%" & Char(10) & "%wantedline_1%" & Char(10) & "%wantedline_2%" & Char(10) & "%wantedline_3%" & Char(10) & "%wantedline_4%"
EndIf
If>{((%p%=1) or (%k%=%lines_count%)) and (%j%=5)}
//output status info
DDEPoke>Excel,%ExcelFilepath%:Progress Board,R1C%testcellnumber%,"%FLine%" & Char(10) & "%wantedline_1%" & Char(10) & "%wantedline_2%" & Char(10) & "%wantedline_3%" & Char(10) & "%wantedline_4%" & Char(10) & "%wantedline_5%"
EndIf
[/code]

tried it like this too:
[code]
"%FLine%" & Char(10) & "%wantedline_1%"
[/code]

I have tested the write line in a number of ways. All of which end up outputting the actual words "Char(10)" into my excel cell.

Snickers · Post by **Snickers** » Tue Jun 03, 2008 2:30 pm

EDIT: gotit

I was not putting the equal sign into the ddepoke

THANK YOU MUCH!

Post by **Marcus Tettmar** » Tue Jun 03, 2008 3:31 pm

Yes, as mentioned previously this works by sending an Excel formula. Hence the equals sign - an Excel formula in a cell starts with an equals sign.

Snickers · Post by **Snickers** » Tue Jun 03, 2008 5:44 pm

One of my lines on the Master Schedule contains a quotation mark. So when my program sets this line to a variable and then tries to send it to excel, it causes my script to freeze.

for example:

Code: Select all

Let>Line1=This is a test message. It will display in the cell.
Let>Line2=This is a test message that is approx. 3" long
DDEPoke>Excel,Book1,R1C1,="%Line1%" & Char(10) & "%Line2%"

The quotation causes the excel output to freeze my script. Is there a way around this since there is no way for me to know if the document will contain a quotation mark?

Is it possible to find a word in a document and replace them all with another. For example, I want to search for quotations and replace them with two apostrophes.

JRL · Post by **JRL** » Tue Jun 03, 2008 7:28 pm

Based on the previous posts I'd try something like:

Code: Select all

Let>Line1=This is a test message. It will display in the cell.
Let>Line2=This is a test message that is approx. 3" long
StringReplace>Line1,"," & Char(34) & ",Line1
StringReplace>Line2,"," & Char(34) & ",Line2
DDEPoke>Excel,Book1,R1C1,="%Line1%" & Char(10) & "%Line2%"

Snickers · Post by **Snickers** » Tue Jun 03, 2008 7:46 pm

JRL wrote:
Code: Select all
StringReplace>Line1,"," & Char(34) & ",Line1

Man that is great! I had written one to do this that involved counting the character using the lenght and midstr....etc.

For people who don't know all of the programming lingo, trying to use search for help ends up being of very little assistance.
I kept doing searches for characters and lines but never used the word "string".

Thank you guys for straightening this out for me.