Link to home
Start Free TrialLog in
Avatar of RWayneH
RWayneHFlag for United States of America

asked on

Removing an absolute value. Folder path.

I the following code I am checking to see if a specific file is open, if it is? Close it, if not? carry on.
Ln7 concerns me because it is an absolute folder location.  Is there a way to write that part out?
I have another procedure that uses that file and it needs to be closed in order to process correctly.
Please advise and thanks.

PS:  Will consider other re-writes too.  It shows my lack of VBA writing skills.


Sub IsSalesOrderDownloadTemplateOpen()
 
 Dim I As Integer
 Dim bIsOpened
    For I = 1 To Application.Workbooks.Count
        If (Application.Workbooks(I).Name = "SALESORDERDOWNLOAD.XML" _
            And Application.Workbooks(I).Path = "C:\_SAP\Extracts\SalesOrd") Then
            bIsOpened = True
            Exit For
        End If
    Next I

 
'  For i = 1 To Application.Workbooks.Count
'   bIsOpened = bIsOpened Or (Application.Workbooks(i).Name = "SALESORDERDOWNLOAD.XML" _
'   And Application.Workbooks(i).Path = "C:\_SAP\Extracts\SalesOrd")
'  Next i

  If bIsOpened Then
    MsgBox "Target save file is open, Closing file so process can use it.", vbOKOnly + vbInformation, "Cannot have: C:\_SAP\EXTRACTS\SALESORD\SALESORDERDOWNLOAD.xml open and run this process.  Close it and try again."
    Application.DisplayAlerts = False
    Windows("SALESORDERDOWNLOAD.XML").Activate
    ActiveWorkbook.Close
    bIsOpened = False
    Application.DisplayAlerts = True
    GoTo 100  'Removed Exit Sub
  Else
   GoTo 100
  End If
100  'Continue knowing that file is not open.
End Sub

Open in new window

Avatar of RWayneH
RWayneH
Flag of United States of America image

ASKER

I may have to leave that in.  I am assuming that I can use a network path like:
\\na\Global-Groups\etc.\etc.\   or even better a sharepoint site?:  
http://departments.haworthinc.com/PLM/data/Lists/IPL%20Data/DispForm.aspx?ID=38&Source=http%3A%2F%2Fdepartments%2Ehaworthinc%2Ecom%2FPLM%2Fdata%2FLists%2FIPL%2520Data%2FIPL%2520data%2Easpx

I do not know how to point to a specific sharepoint file... and how to add the code if a user does not even have access to the particular site.   I am good with leaving it in, and need to change that Ln7 to point to sharepoint location.
SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RWayneH

ASKER

So what you are saying is it would be something like:?

If  Windows("SALESORDERDOWNLOAD.XML").Activate = False
    continue
End if

If  Windows("SALESORDERDOWNLOAD.XML").Activate = True
   Close it without saving changes.
   Continue
End if
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RWayneH

ASKER

How would we add a if true, close file without saving changes...  I do not want alerts to stop and ask user if it is ok ....  Looks like this will work much better than the original one I had.  Thanks!!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RWayneH

ASKER

Thanks for the help.