Welcome to the VanDyke Software Forums

Join the discussion today!


Go Back   VanDyke Software Forums > Scripting

Reply
 
Thread Tools Rate Thread Display Modes
  #1  
Old 02-01-2018, 09:57 AM
bstedh bstedh is offline
Registered User
 
Join Date: Jan 2018
Posts: 41
Excel Workbook already open test

I am putting some of my function output into excel. I have that working quite well but need a test to see if the workbook is already open as I am using the same workbook every time because this is for information that overruns the screen in CRT and the word wrap makes it undecipherable. Currently it opens another window every time in readonly because the workbook is already open. It needs to stay open as it is being used to display information.

Here is the code I am using to open the workbook and getting it ready to receive information. It creates a second sheet an deletes the first sheet to clear any info that is existing in the workbook. It then colors every other line light grey for readability and also colors any lines red that have an error condition listed.

Code:
Function ReadoutputToEX
	Set objExcel = CreateObject("Excel.Application")
	objExcel.DisplayAlerts = False
	objExcel.Visible = True
	objExcel.WindowState = 3
	Set wb = objExcel.Workbooks.Open(WBpath)
	Set sh = wb.Sheets(1)
	wb.Sheets.Add()
	sh.delete
	Set sh = wb.Sheets(1)
	objTab.screen.Send "XXXXXXXXXXXXX" & vbCr
	Output = f5Output
	Output = Trim(Output)
	Output = f6RMspace(Output)
	Output = Replace(Output,"=","")
	arrOutput = Split(Output,vbCr)
	R = 1
	C = 1
	E = 1
	For Each Line In arrOutput
		arrCell = Split(Line," ")
		For Each Col In arrCell
			sh.Cells(R,C) = Col
			C = C + 1
		Next
		If E = 2 Then
			sh.Rows(R).Interior.Color = RGB(220,220,220)
			E = 0
		End If
		If InStr(Line,"disabled") Then sh.Rows(R).Interior.Color = RGB(255,190,190)
		R = R + 1
		C = 1
		E = E + 1
	Next
	objExcel.Visible = True
	sh.Columns.AutoFit
	sh.Rows.RowHeight = 15
	sh.Range("F4").Select
	objExcel.ActiveWindow.FreezePanes = True
'	objExcel.DisplayAlerts = True
	wb.Save


	ReadoutputToEX= "output placed in excel"
End Function
__________________
Version 6.7.3 (build 292)
Reply With Quote
  #2  
Old 02-01-2018, 10:54 AM
bstedh bstedh is offline
Registered User
 
Join Date: Jan 2018
Posts: 41
I found a partial fix. I moved the set application and set workbook up to the top of the script to make it global so the workbook stays active throughout the script. However, I still need the test in case the workbook was opened separately or the script crashed for some reason leaving it open.
__________________
Version 6.7.3 (build 292)
Reply With Quote
  #3  
Old 02-01-2018, 12:43 PM
ekoranyi ekoranyi is offline
VanDyke Technical Support
 
Join Date: Jan 2017
Posts: 654
Hi bstedh,

I've found a previous thread that seems to address the issue.

https://forums.vandyke.com/showthread.php?t=3192

The below code seems to be able to identify if an Excel file that contains "example" in the name is open. I hope this is a good starting point for you.

Code:
Sub Main
 set objShell = CreateObject("Wscript.Shell")
 Set objWord = CreateObject("Word.Application")
 Set colTasks = objWord.Tasks
 u = 0
 For Each objTask in colTasks
 strName = LCase(objTask.Name)

 If Instr(strName,"example") Then
 u = 1
 End If
 Next
 If u = 0 Then
 msgbox "close"
 End If
 If u = 1 Then
 msgbox "open"
 End If
 objWord.Quit
 end sub
__________________
Thanks,
--Eric

VanDyke Software
Technical Support
support@vandyke.com
(505) 332-5730
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 02:26 PM.