• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Blank Macro

Christina Calkins

Christina Calkins

Member
I am trying to find the wording for a command. I am working in a program called IBM WRQ Reflection. It's our rating system at my job. I am setting up a macro that will allow me to enter in information into certain fields from and excel spreadsheet. There is a spot where sometimes I will have information to go into this field and sometimes I will not. I am assuming that the wording would be similar no matter what program you are working in. So my question is, what wording would I use to state that if there is a blank or nothing to enter, go to ______?

Thank you for your assistance.

Christina
 
Hi Christina ,

Your problem descriptions seems somewhat unclear to me.

Are you entering information directly into worksheet cells ?

Are you using a Worksheet_SelectionChange or a Worksheet_Change event procedure or are you running some other macro ?

Are the cells which may be left blank one or more than one ? If so , which ones are they ?

Can you not use simple Data Validation to validate the cells which need to be filled in , and ignore blanks ?

It would help if you could explain a little more , or even better upload your workbook.

Narayan
 
I write the macro on an excel worksheet and then copy and paste into my macro button in IBM. I only use one worksheet to copy and paste from. Below is the screen that I am writing the macro for. I have tried to upload my spreadsheet that has the macro into DropBox however it keeps saying that it doesn't support my browser and I have the most updated browser so I am at a lose for what to do. So I paste the macro below that image.


upload_2014-10-2_11-1-17.png



counter = 0
For Each Element In myarray
.WaitForEvent rcEnterPos, "30", "0", 4, 19
TransmitANSI Element
.TransmitTerminalKey rcIBMTabKey
.TransmitTerminalKey rcIBMTabKey
.TransmitTerminalKey rcIBMTabKey
.TransmitTerminalKey rcIBMTabKey
.WaitForEvent rcEnterPos, "30", "0", 7, 32
TransmitANSI myarray2(counter)
.TransmitTerminalKey rcIBMTabKey
.WaitForEvent rcEnterPos, "30", "0", 7, 60
TransmitANSI myarray3(counter)
.SetMousePos 14, 19: TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol
.WaitForEvent rcEnterPos, "30", "0", 14, 19
TransmitANSI myarray4(counter)
.TransmitTerminalKey rcIBMFieldExitKey
.WaitForEvent rcEnterPos, "30", "0", 14, 31
TransmitANSI myarray5(counter)
.TransmitTerminalKey rcIBMTabKey
.WaitForEvent rcEnterPos, "30", "0", 15, 19
TransmitANSI myarray7(counter)
.TransmitTerminalKey rcIBMTabKey
.WaitForEvent rcEnterPos, "30", "0", 16, 19
TransmitANSI myarray8(counter)
.WaitForEvent rcEnterPos, "30", "0", 16, 25
.TransmitTerminalKey rcIBMFieldExitKey
.TransmitTerminalKey rcIBMEnterKey
.SetMousePos 24, 2: TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol: mystring = GetFieldText(24, 2, 4)
.WaitForEvent rcEnterPos, "30", "0", 24, 2
If mystring = "Lane" Then
MsgBox ("REVIEW DUPLICATE")
Else
.TransmitTerminalKey rcIBMF5Key
End If
counter = counter + 1
Next Element


Thank you,
Christinaupload_2014-10-2_11-1-17.png upload_2014-10-2_11-1-17.png upload_2014-10-2_11-1-17.png
 
Below is the second part of the macro. As you can see, sometimes I have information to go into myarray 6 and sometimes I do not.

Customer city/st city/st Wkly Vol 1st Comment 2nd Comment Lane Type Eff Date
CSCOH COLUOH DECAIL 2.00 1-2 LOADS PER WEEK Per Store W 081814
CSCOH COLUOH SPARSC 2.00 1-2 LOADS PER WEEK W 081814
 
Christina,
I wouldn't really call WRQ Reflection a "program". It is just a terminal emulator (see below). Perhaps you can make sure your spreadsheet data is never null and always contains at least a space. That may help upload the data.

Thanks,
Ronnie


Publisher's Description
From WRQ:
Reflection for the Web provides terminal emulation from a web browser. With this server-based solution you can connect local and remote users to applications on IBM, HP, UNIX, and OpenVMS hosts. You can also:
  • Deploy, configure, and track web, Windows, and PC X-server sessions from the same web-based management console.
  • Use the Java-based proxy server to provide maximum encryption—AES and 3DES—over SSL/TLS.
  • Programmatically customize sessions using a rich set of APIs and Java or JavaScript.
Capabilities like these—designed specifically to boost IT efficiency and user productivity—have become the hallmark of Reflection.
 
You are correct it is not a "program" it is our rating system. I was trying to not have to put a space in every field that has a "Blank". Right now I have it set up that way and was just wondering if there was any other string of words that could be used instead on a space to tell it to go to the next if there isn't anything there. I have tried "IF" statements and they do not seem to go well.

Thanks.
 
Christina,
WRQ Reflection is not your rating system either. It is just a "connection" to it from the terminal you are using. I have used Reflection with Oracle Financials also. It is just a connection tool.
Do you have other macros on your rating system? Looking at those may help. It is likely that the Excel macros are not exactly compatible with your rating system.

Thanks,
Ronnie
 
We have terminals in reflections that we use to rate, so as far as I know it is our rating system. I log into reflections everyday and use that screen to rate and do everything else that I need to do. The macros are compatible with our rating system, I have been using them for quite some time now and have become very good at it. I just need to know if anyone else knew of a different wording besides using a space that I could use. Thank you though. Have a good day.
 
Christina,
Sorry if I was off subject. Could you use an IF statement like you have near the bottom of your code, but check for ""? Or is the main issue a compatibility issue with your browser?

Thanks and Sorry,
Ronnie
 
As it is above right now, you have to put a space in the field for it to recognize that there is "something" there. I don't want to have to do that every time I don't have something there. Let me walk you through the steps that I have to do first. I have multiple HUGE spreadsheets that I use for different macros. I write the macros in excel and then copy and paste them into IBM WRQ Refelction terminal. I then hit my run macro button. However I can't upload the spreadsheet for you to look at because DropBox is being dumb. So I will have to manually walk you through.

The first part of my macro (which is a different one than the above) is below:


Cust Code (6 Characters Max) Name (25 Characters Max) Short Name (Max 8 letters) Address Address (If you don't have a 2nd address, put a space in this field)
PPISEL HQ/BILLING ADDRESS HQ 2014 SECOND STREET





This is the info that I plug into the spreadsheet. Then on that same spreadsheet to the right I write my macro. Part of it is below:

TransmitANSI myarray5(counter).SetMousePos 7, 13: TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol.WaitForEvent rcEnterPos, "30", "0", 7, 13TransmitANSI myarray6(counter)

Then to the right of that I connect all of the items together using formulas.

=CONCATENATE(AG2,B2,AG2)
=IF(B2=0," ",AH2)
=CONCATENATE(AG2,C2,AG2)
=IF(C2=0," ",AJ2)
=CONCATENATE(AG2,D2,AG2)
=IF(D2=0," ",AL2)

and so on. I then copy the whole 2nd part of that macro:

ransmitANSI myarray5(counter).SetMousePos 7, 13: TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol.WaitForEvent rcEnterPos, "30", "0", 7, 13TransmitANSI myarray6(counter)

and paste it into my terminal macro.

I just want to be able to write verbiage that will allow me to not have to put a space in the cell when ever I don't have information to go there.
 
I actually figured it out for those who have a similar question later on. You can use an "if" statement, it works.

TransmitANSI myarray5(counter)
.TransmitTerminalKey rcIBMTabKey
.WaitForEvent rcEnterPos, "30", "0", 15, 19
TransmitANSI myarray6(counter)
.SetMousePos 15, 31: TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol: mystring = GetFieldText(15, 31, 2)
If mystring = " " Then
.SetMousePos 16, 19: TerminalMouse rcLeftClick, rcMouseRow, rcMouseCol
Else
.TransmitTerminalKey rcIBMTabKey
End If
.WaitForEvent rcEnterPos, "30", "0", 16, 19
 
You must log in or register to reply here.
Back
Top