Delete/Negate PromptMsg

[thelastlizard]

Hello world,
I just discovered and joined Tek-tips as it seems to be the perfect place for me, and as it turns out this would be my first time asking for support online. In short, I'm a total n00b.

My question has to do with the following code - I've inherited it from someone long gone, and I'm unsure of how to remove the Message Prompts at the beginning. When I negate them, I seem to cause an error between lines 59-64. How do I amend the code properly? Thanks in advance!


1 Sub Main
2 '**************** Get the main system object *************************
3 Dim Sessions As Object
4 Dim System As Object
5 Set System = CreateObject("EXTRA.System") ' Gets the system object
6 If (System is Nothing) Then
7 Msgbox "Could not create the EXTRA System object. Stopping macro playback."
8 exit sub
9 End If
10 Set Sessions = System.Sessions
11 If (Sessions is Nothing) Then
12 Msgbox "Could not create the Sessions collection object. Stopping macro playback."
13 exit sub
14 End If
15
16 '------------------------------------------------------------------------------------------
17 '**************** Set the default wait timeout value ******************
18
19 System.TimeoutValue = 60000 ' milliseconds
20
21 '------------------------------------------------------------------------------------------
22 '**************** Get the necessary Session Object ********************
23 Dim Sess0 As Object
24 Set Sess0 = System.ActiveSession
25 'Set Sess0 = GetObject("C:\Documents and Settings\rbaut01\Desktop\r-pdc_manual release.xls")
26 If (Sess0 is Nothing) Then
27 Msgbox "Could not create the Session object. Stopping macro playback."
28 exit sub
29 End If
30
31 '------------------------------------------------------------------------------------------
32 '********************** Open the Excel file ***************************
33 dim vblExcel as object
34 dim vblSheet as Object
35 dim vblexcelprog as object
36
37 '------------------------------------------------------------------------------------------
38 dim PromptMsg
39 dim PromptMsg2
40 dim BoxMsg
41 dim BoxMsg2
42 dim DefaultFile
43 dim DefaultSheet
44
45
46 PromptMsg = "Enter the Directory for your Excel File (for example, C:\Documents and Settings\rbaut01\Desktop\r-pdc_manual release.xls):"
47 BoxMsg = "EXCEL FILE NAME!"
48 DefaultFile ="C:\Documents and Settings\rbaut01\Desktop\r-pdc_manual release.xls"
49
50 FileName = InputBox$ (PromptMsg, BoxMsg, DefaultFile)
51
52 PromptMsg2 = "Enter the name of the Worksheet with the data from your Excel File (for example, Sheet1):"
53 BoxMsg2 = "EXCEL WORKSHEET NAME!"
54 DefaultSheet = "Sheet1"
55
56 WorksheetName = InputBox$ (PromptMsg2, BoxMsg2, DefaultSheet)
57
58 '***Open the file with the data**
59 set vblExcel = getobject(FileName)
60
61 set vblexcelprog = vblexcel.parent
62 '***Make the file visible***
63 vblexcelprog.Windows(vblExcel.name).Visible = true
64 set vblSheet = vblexcel.worksheets(WorksheetName)
65
66 '------------------------------------------------------------------------------------------
67 '******************* Set the starting row in Excel ********************
68 Dim PromptX
69 Dim BoxMsgX
70 Dim DefaultX
71
72 PromptX = "Enter the Starting Row for your data."
73 BoxMsgX = "STARTING ROW!"
74 DefaultX = 2
75
76 RowX = InputBox$ (PromptX, BoxMsgX, DefaultX)
77
78 Msg1=" Welcome to the Players Club." & Chr(10)
79
80
81 Msg1=Msg1 + Chr(10) + " Click OK continue, or cancel to exit the macro" & Chr(10)
82
83 Click = Msgbox (Msg1, 1+64+0, "Spreadsheet format")
84
85 If Click = 2 then
86 GoTo EndMacro:
87 End if
88
89 '------------------------------------------------------------------------------------------
90 '********* Begin the process of reading through the Excel File ********
91
92 do until vblSheet.Cells(RowX, 1) = ""
93 '*Make sure the cell in the first column is not blank - Quit if it is
94 If vblSheet.Cells(RowX, 1) = "" then
95 exit do
96 End if
97
98
99 InputHeader:
100
101 Sess0.Screen.sendkeys("<pf5>")
102 Sess0.Screen.WaitHostQuiet(1)
103 Sess0.Screen.PutString vblSheet.Cells(RowX, 256),9,10
104 Sess0.Screen.PutString vblSheet.Cells(RowX, 2),9,5
105 Sess0.Screen.PutString vblSheet.Cells(RowX, 3),9,20
106 Sess0.Screen.PutString vblSheet.Cells(RowX, 4),9,28
107 Sess0.Screen.PutString vblSheet.Cells(RowX, 5),9,70
108 Sess0.Screen.PutString vblSheet.Cells(RowX, 6),9,82
109 Sess0.Screen.PutString vblSheet.Cells(RowX, 7),9,93
110 Sess0.Screen.PutString vblSheet.Cells(RowX, 8),9,101
111 Sess0.Screen.PutString vblSheet.Cells(RowX, 9),9,108
112 Sess0.Screen.PutString vblSheet.Cells(RowX, 10),9,114
113 Sess0.Screen.sendkeys("<enter>")
114 Sess0.Screen.WaitHostQuiet(5)
115
116 ' GoTo ErrorCheck:
117
118
119 'ErrorCheck:
120
121 ' E102 = Sess0.Screen.GetString (25,2,10)
122 ' If E102 = " " then
123 ' Sess0.Screen.WaitHostQuiet(100)
124 ' GoTo ErrorCheck:
125 ' End if
126 ' E109 = Sess0.Screen.GetString (25,2,104)
127 ' If E109 = "MEC060 W 814 Review price change. Percentage change in price exceeds limit for some PA(s) and/or UPC(s)." then
128 ' GoTo Finish:
129 ' Else
130 ' If (E109 = "MEC065 I 623 Price change created for (CORPCD, UNIT TYPE, ALL P/A(S)) " OR E109 = "MEC065 E 356 The highlighted description contains an invalid character. (É) " OR E109 = "MEC060 I 637 Price area created for (CORPCD, UNIT TYPE, DEFAULT P/A) ") then
131 ' GoTo Finish:
132 ' Else
133 ' vblSheet.Cells(RowX, 14) = E109
134 GoTo Finish:
135 ' GoTo EndMacro:
136 ' End if
137 ' End if
138
139 '**************************************************************************************************
140
141 Finish:
142
143 '*enter the date and time into the excel file
144 vblSheet.Cells(RowX, 12) = date()
145 vblSheet.Cells(RowX, 13) = time()
146 vblSheet.Cells(RowX, 14) = E109
147
148 '*Increment x to move to next record
149 RowX=RowX+1
150
151 '*continue with the process until a blank cell is found in the first column
152
153 loop
154
155 '**************************************************************************************************
156
157 EndMacro:
158
159 End Sub

 

[SkipVought]

hi,

This code traps when the System or Session object is not created in the Set statement. If either of these objects is not created, then the emulator cannot be referenced. Hence the Exit Sub.

Set System = CreateObject("EXTRA.System") ' Gets the system object

If (System is Nothing) Then 
   Msgbox "Could not create the EXTRA System object. Stopping macro playback." 
   exit sub 
End If 

Set Sessions = System.Sessions 
If (Sessions is Nothing) Then 
   Msgbox "Could not create the Sessions collection object. Stopping macro playback." 
   exit sub 
End If

Regarding lines 59-64: This has to do with assigning the Excel application objects. Here's some tips:

1. you'd be much better off having the workbook/sheet OPEN BEFORE the process begins. Having a user enter data (pron to error, misspelling/capitalization etc)

2. if you must, then a) assign an Excel Application Object b) use the GetOpenFileName() function from Excel to allow the user to drill down & select a workbook. VB Help has some nice examples. Post back for more help.

3. I do a LOT of screen scrapping. I run ALL my scrapes from Excel. Each screen that i scrape (probably 4 dozen) loads into a sheet like a table. Each screen has a tabular description of the location of fields, size, type. I scrape ALL the data from each screen into the appropriate Excel table and then from there decide what to do to give the user what they need. Yours may be a one time thing. It usually becomes a repeated thing. That's how I developed my approch.

Hope this helps.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[thelastlizard]

Skip, thanks very much for your thoroughness and insight! I'm probably phrasing my question inadequately, but I'm really looking to stop the message prompts that pop up when the macro is ran. Basically, I want to turn them off to avoid the type of user-error you eluded to - misspelling, capitalizations, etc.

I'm not sure what you mean by screen scraping, but I feel that I may be missing something extremely relevant to what I'm doing... We are basically using the macro to input consolidated information from Excel back into Extra...

 

[SkipVought]

Getting rid of a message will not fix the problem. You must figure out why the System object or Session object is not begin properly created!

Avoiding spelling errors has nothing to do with the having message popup or not. The messages are informational feedback. Deal with the issues!

With respect to opening these workbooks interactively, I'd suggest using a WORKBOOK/SHEET LIST instead, but maybe I don't understand your process.

Screen scraping is a term used for the process of getting data out of a terminial emulator. I underatand that you are loading data from Excel to your mainframe system via the Extra terminal emulator.

Let me make another comment regarding the default wait time, but telling a story. You get in your car in the morning to go to work. Your vehicle is a well designed SYNCHRONOUS system. You press the accellerator and the car increases in speed. Tiy press the break and it slows the vehicle. It all happens in a synchronized manner.

You would not even consider as a remote possibility, deciding that whenever you got to a stop sign or traffic light, you would simply wait 6000 milliseconds before preceeding, would you. And why not? Although your auto is a synchronous system, you are operating your vehicle in an asynchronous system. In those instances, you must wait until you receive feedback from the asynchronous system (viewing the roadway for absence of approching traffic, or viewing the traffic light for a green signal) before proceeding.

Why shouldn't this logic apply here? When you SendKeys to the asynchonous system via the terminal emulator, that system goes off and does it's thing. You don't really know how long it's going to be until the system completes it's asynchronous process and indicates that via the terminal emulator. You need to wait at the traffic light until it turns green. One way to do that in Extra is to WaitForCursor at the screen rest position.

    With oScrn
 
       .Area(17, 28, 17, 28) = "S"
       .SendKeys ("<ENTER>")

       Do Until .WaitForCursor(4, 3)
          DoEvents
       Loop


    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[remy988]

what line are you negating?

 

[thelastlizard]

The messagep-rompts are basically only there to ask "Hey, what file am I working with here?" and we would enter/confirm the path (a default path and file is already suggested in the prompt). Then they'll ask, "Hey what sheet should I read off of this file?", and, "What row do I start at?".

So, I guess my question should be: how do I code the macro to just pull up that specific file, specific sheet, and specific row without prompting the user to confirm? When I tried deleting the prompts out of the code, I end up getting an error between lines 59-64. Otherwise the macro does not encounter any problems with lines 59-64, assigning the Excel application objects.

The default wait time was something I was also going to inquire about at a later time, but what you said makes PERRRFECT sense. Being that I inherited this macro from someone else, I wasn't sure if decreasing it would adversly affect anything. Unfortunately, I'm also new to this stuff so I didn't even know about the WaitForCursor...command? Makes sense though and it sounds like the best way to code this thing, so I'll try my best to amend that into the macro.

Thanks again, Skip!

 

[remy988]

this is the code i use when i know exactly where the file is located.

i don't know how to check to see if the file actually does exist in the file directory nor do i know how to check if the file is already open.
so i'm not sure if hardcoding the location of the file is a good idea. but this should get you started.

Sub Main

    myUser = Environ("username")
    xlFile = "C:\Users\" & myUser & "\Desktop\Test.xlsx"
    Dim xl as object
    Set xl=CreateObject("Excel.Application")
    xl.visible = True
    xl.workbooks.open xlFile
        
    myCell = xl.range("a1")
    msgbox myCell

End Sub

 

[SkipVought]

I had previously suggest using the [highlight]GetOpenFilename[/highlight] method of the Excel application object. Your users can drill dow to ANY file that they have acces to on their PC or on a network.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DwMcC]

Here's a routine I reuse all the time for Excel connections that's been cobbled together over the last few years from experience and forums...maybe it will be useful to you.

'In your declarations add this:
Declare Sub OpenExcel
Declare Sub CloseExcel
Global S0 As Object, Sys As Object, Sess As Object, SCR As Object, filepath$, AppExcel As Object, wbExcel As Object
Global r As Long, x As Long, y As Long,  iSht As Object, xlApp As Object, xlSheet As Object, MyRange As Object
Global DT As Object
'---------------------------------------------------------------------------------------------
Sub Main() 
'* 
    Call MainInit
    'Returned from MainInit so We know we have a valid session now...
'*    
    Call OpenExcel
'*    
    'do some other stuff here
'*
    Call CloseExcel
'*
    'Let's us know we have successfully reached the end of the routine
    MsgBox("Macro is complete")
End Sub
'---------------------------------------------------------------------------------------------
Sub MainInit
    'Get the main system object
    Set Sys = CreateObject("EXTRA.System")	' Gets the system object
    If (Sys is Nothing) Then
        Msgbox "Could not create the EXTRA System object.  Stopping macro playback."
        STOP
    End If
    Set Sess = Sys.Sessions
    If (Sess is Nothing) Then
        Msgbox "Could not create the Sessions collection object.  Stopping macro playback."
        STOP
    End If 
    Sys.TimeoutValue = 500
    'Get the necessary Session Object    
    Set S0 = Sys.ActiveSession
    If (S0 is Nothing) Then
        Msgbox "Could not create the Session object.  Stopping macro playback."
        STOP
    End If
    Set SCR = Sess.screen
    If Not S0.Visible Then S0.Visible = TRUE
    S0.Screen.WaitHostQuiet(1000)
End Sub
'---------------------------------------------------------------------------------------------
Sub OpenExcel
    filePath="C:\WHATEVERMYFILENAMEISHERE.xlsx"
    Set AppExcel = CreateObject("Excel.Application") 
    If AppExcel is Nothing Then
        msgbox("Error, could not create the Excel Spreadsheet.")
        Exit Sub
    End If
    Set wbExcel = AppExcel.WorkBooks.Open(filepath)
    AppExcel.DisplayAlerts = False
    AppExcel.Visible = True
    Set xlSheet = wbExcel.ActiveSheet
    Set iSht = wbExcel.Sheets(1)
    Set MyRange = wbExcel.ActiveSheet.Range("A:A")
    With wbExcel.ActiveSheet
        Set MyRange = .Range("A2:A65536").Resize(AppExcel.CountA(.Range("A2:A65536")))
    End With
'Write TO Excel Objects
	On Error GoTo 0 'Resume normal error processing 
    Set 1sht = wbExcel.Sheets(1)'rename set value to anything you want, but declare in the declarations section, see "iSht" for example
    Set 2sht = wbExcel.Sheets(2)
    Set 3sht = wbExcel.Sheets(3)
    Set 4sht = wbExcel.Sheets(4)
    Set 5sht = wbExcel.Sheets(5)
    Set 6sht = wbExcel.Sheets(6)       
    'Establish how many rows exist on the first spreadsheet
'Write FROM Excel Objects; 
    Set DT =  wbExcel.ActiveSheet.Range("A:A")'Date 
    iCols = SCR.Cols
    iRows = SCR.Rows
End Sub
'---------------------------------------------------------------------------------------------
Sub CloseExcel
    'This keeps Excel 'noise' (constant dialog boxes) from appearing during the save/quit cycle
    AppExcel.DisplayAlerts = false
    'Save the spreadsheet
    wbExcel.SaveAs "C:\SheetName_Completed_" & Format(Now, "MM-dd-yy_h-mm-ss") & ".xlsx"
    'Quit Excel cleanly
    wbExcel.Close
    AppExcel.Quit
    Set wbExcel = Nothing
    Set AppExcel = Nothing
End Sub
'---------------------------------------------------------------------------------------------
Sub Writetoandfromexcelexample
'Write to the spreadsheet
  1sht.Select 'select the spreadsheet to write to
  Dim TEcount As String, DBcount As String
  TEcount =  Trim(Sess.Screen.GetString(11, 43, 6))'Transmittal Errors Count
  DBcount =  Trim(Sess.Screen.GetString(12, 43, 6))'duplicate batches Count
    Mainsht.Cells(rowcount,3) = TEcount
    Mainsht.Cells(rowcount,6) = DBcount  
  'don't forget some kind of loop or For that sets the rowcount to write to
'Write from the spreadsheet  
	Sess.Screen.PutString DT.Rows(r).Value, 22, 33 '"r" being a variable in a loop or For to know what row to be on  
End Sub

If you bring that sentence in for a fitting, I can have it shortened by Wednesday!