Welcome to the VanDyke Software Forums

Join the discussion today!

Go Back   VanDyke Software Forums > Scripting

Thread Tools Rate Thread Display Modes
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.

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)
	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
		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
	objExcel.Visible = True
	sh.Rows.RowHeight = 15
	objExcel.ActiveWindow.FreezePanes = True
'	objExcel.DisplayAlerts = True

	ReadoutputToEX= "output placed in excel"
End Function
Version 6.7.3 (build 292)
Reply With Quote
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
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.


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.

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
 If u = 0 Then
 msgbox "close"
 End If
 If u = 1 Then
 msgbox "open"
 End If
 end sub

VanDyke Software
Technical Support
(505) 332-5730
Reply With Quote

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 08:48 AM.