Topic: Problem getting Excel workbook if the file is open by another user

[Rabbit]

I'm trying to use lisp routines written by Terry Miller, GetExcel.lsp.

When using the following, I get an automation error:

Code: [Select]

(setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
  (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
Is there a way to get the workbook information even if the file is opened by another user?

If not, then would using lisp to make a copy of the file in another location, get the information, then deleting the file be an acceptable work around?

Also, the person creating the Excel file is putting the revision date at the end of the file name, i.e.  MyFile 11.05.12.xlsx.  Each time a revision to the file is made, a new version is created with a different file name, i.e. MyFile 11.06.12.xlsx.  Is there a way to do a wcmatch for an file open/copy call, for which I can compare dates (file name or actual file save date) to see which version is newest?

All help is appreciated,
Rabbit

[Lee Mac]

Is there a way to do a wcmatch for an file open/copy call, for which I can compare dates (file name or actual file save date) to see which version is newest?

Use wcmatch on each item in the list of files returned by vl-directory-files for a given directory.

Scratch that, just use the pattern parameter with the vl-directory-files function:

Code: [Select]

(vl-directory-files <Your Directory> "MyFile*.xlsx" 1)
Then sort the list of files returned by date contained in the filename.
For this task, consider the following sorting function:

Code - Auto/Visual Lisp: [Select]

1. (defun _getlatestfile ( dir )
2.     (car
3.         (vl-sort (vl-directory-files dir "MyFile*.xlsx" 1)
4.             (function
5.                 (lambda ( a b / x y )
6.                     (cond
7.                         (   (/= (setq x (substr a 14 2)) (setq y (substr b 14 2)))
8.                             (> (atoi x) (atoi y))
9.                         )
10.                         (   (/= (setq x (substr a 8 2)) (setq y (substr b 8 2)))
11.                             (> (atoi x) (atoi y))
12.                         )
13.                         (   (> (atoi (substr a 11 2)) (atoi (substr b 11 2))))
14.                     )
15.                 )
16.             )
17.         )
18.     )
19. )

The above is quickly written & untested and assumes the exact filename format:

Code: [Select]

"MyFile MM.DD.YY.xlsx"

[Rabbit]

HOLY CRAP!!!  I just found out something new.If I click on colored items in your post it goes to a lisp encyclopedia.  How'd you do that?  WAY COOL!!!

(settling down now)

Now to see what I can learn from you.  I didn't know about vl-directory-files.

I guess my best bet is to create a copy of the Excel file and get the info from it.

Thanks,
Rabbit

Edit - I found out how GeSHi

[irneb]

Yep, say thanks to the Swamp for adding that to the forum. If you tell it what language the code is written in it colours the code and links to known keywords automatically.
 
Anyhow, th temp copy of the file might work. You could try the vl-file-copy function. But have you tried the Excel Open method's Readonly option? http://msdn.microsoft.com/en-us/library/ff194819.aspx