Extract embedded file from table to folder

[jj]

I have an table in access with embedded excel files

I want to extract them to a folder with automation - the only way I know is
to create af Form, and then automatically open and close the form. In the
on-open event I run the following code:

Dim oXL As Object
'Dim strFileSpec As String

'Activate the embedded document in an instance of Excel
'MyOle is a bound object on the form
Me!MyOle.Action = acOLEActivate
'get hold of the instance - assumes that there is only the one
'Instance of Excel running

Set oXL = GetObject(, "Excel.Application")

'Save the document
If Len(Dir(CurrentProject.Path & strFileSpec & ".xls")) > 0 Then
Kill (CurrentProject.Path & strFileSpec & ".xls")
End If
oXL.Workbooks(1).SaveAs CurrentProject.Path & strFileSpec & ".xls"
'Close it, leaving Excel running ready to process the next record
oXL.Workbooks(1).Close False
'Or close Excel if you are just exporting one document
oXL.Quit

This work - But my Very Big problem is that when it reach: Me!MyOle.Action =
acOLEActivate the Security form from excel opens - because the ole Object
contains macros. And this operation need
a manual handling :-(((( Is there a way that I can avoid that - for example
disable the macros so this will not appear?

Thx
JT

 

[Guest]

You can disable the macro security in Excel, at least long enough to allow
you to finish extracting your files. Whether or not you wish to leave it
disabled on a permanent basis is up to you.

In Excel 2003, click on:
Tools > Macro > Security

Change the setting to Low. Then switch to the Trusted Sources tab. Place
checks in the options shown at the bottom, as appropriate.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[jj]

Hi Tom

Thx for your answer - Unfortunately this does not help me - since it must be
automatic from VBA - no manuel process

By the way - I use office 2000

/JJ

 

[Guest]

So your need is not a one-time thing? If it could be automated from VBA, I
think the people who spend their time creating viruses would certainly take
advantage of such a security hole.

Suggest you repost to an Excel newsgroup, since macro security in Excel
doesn't seem to have a lot to do with Access. You *might* (?) be able to use
VB script to temporarily lower macro security settings, but that would mean
starting your application using this script each time, not by double-clicking
on the file or by first opening Access. I believe I have seen this work with
Access, but it's something I only played with once or twice a few years ago.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Stephen Lebans]

See:
http://www.lebans.com/oletodisk.htm
NEW - Feb 06/2006 ExtractInventoryOLE.zip A2K or higher ONLY! This
version saves the entire contents of a table containing OLE Objects to disk.
Does NOT require the original application that served as the OLE server to
insert the object. Supports all MS Office documents, PDF, All images
inserted by MS Photo Editor, MS Paint, and Paint Shop Pro. Also supports
extraction of PACKAGE class including original Filename. Contains function
to produce a full Inventory of the OLE field including LINKED path and
Filenames. Uses Structured Storage API's to read the actual contents of the
field. Version BETA 1.56


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[jj]

Hi Stephen

Thx for your answer - I have actually seen your work before - but here you
need to save a dll on the computer - and I need a method where my collages
can copy the database from the network
to their own computer, at that it will still work - and it won't if they
don't move the dll as well :-(

/JJ

 

[Stephen Lebans]

Well then you will have to either:

1) Go back to your Automation route as per your original post

or

2) Copy my DLL into a Long Binary field within your MDB. At runtime, copy
the DLL from the Long Binary field into the same folder as the MDB. No DLL
registration required.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[jj]

Hi Stephen

Thx again - I wasn't aware of the possiblity to store as Long Binary field -
I will try that - but I have a problem getting you sample database to work -
I have copied the dll to both Windows\system32, and to the folder where the
sample database i stored, but when I run the form for extracting a single
oleobject - it comes up with the msgbox - can't find the SSGetContents.dll -
any Idea - what might be the reason :-l

thx /jj

 

[Stephen Lebans]

Can you run the extraction tool for the entire table? Does it error out with
the same message?
Run the extraction routine and place a breakpoint on the LoadLib function in
the modGetContentsStream code module. Is the code failing on the the line
containing the CurrentDB method?

What version of Access and WIndows are you using?

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[jj]

Hi Stephen

It fails here: hLibStrStorage = LoadLibrary(CurrentDBDir() &
"SSGetContents.dll") - allthough CurrentDBdir is the right directory and
SSGETContents.dll is inside the folder. :-l

I run A2003 and Windows XP

thx again
/JJ

 

[Stephen Lebans]

Just to test, change the call to CurrentDB and hardcode the path instead.

Do you by chance have a space in the folder name?

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[jj]

Hmm - I'm not sure what you mean by change to CurrentDB - where?!

I have tried with this - but it doesn't work.

hLibStrStorage =
LoadLibrary("C:\Tutorial\ExtractInventoryOLEver75\SSGetContents.dll")

/JJ

 

[Stephen Lebans]

I do not know what else to tell you. You state that the DLL is copied into
the folder yet when you hardcode the path to the DLL the call is failing.
Technically, that is simply not possible.

Just for clarification - did you originally have a space in the folder name?
Can you browse via the File Explorer window and see the DLL in the proper
folder?

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.