martmac
asked on
URGENT - Need modification to ole export function
I have been experimenting with an mdb from Stephen Lebans to export a large number of ole objects from a database. It is without doubt superb, but I need to modify a field in each row to indicate where the file has been saved so I can use the output to still open the documents from the database.
Here's the code
What I need to do is update a field called DocPath to reflect where the document has been saved, but I have no idea how to achieve this, can anyone help.
Code is from http://www.lebans.com/oletodisk.htm (Stephen Lebans) take a trip there it has some excellent tools.
Thank you in advance
Private Sub ExportOLE()
' Array of Bytes to hold the contents of the OLE field
Dim a() As Byte
' For copy of OLE field before processing. Use to debug
' and examine new/unsupported objects.
Dim b() As Byte
' Temp vars
Dim x As Long
Dim lTemp As Long
Dim sl As String
Dim blRet As Boolean
Dim lngRet As Long
Dim DocPath As String
' File extension of OLE object
Dim sExt As String
' Load our Structured Storage Library
' Let's see if the StrStorage.DLL is available.
blRet = LoadLib()
If blRet = False Then
' Cannot find StrStorage.dll file
Exit Sub
End If
' Create instance of cDIBSection in the modGetContentsStream module
'Dim ds As New cDIBSection
CreateDS
' Counter
Dim lTotalRecsExported As Long
On Error GoTo ERR_export
' This is an optional param we pass to fGetContentsStream.
' It will contain the original file name of the
' object when embedded as a Package.
Dim PackageFileName As String
' File number
Dim iFileHandle As Integer
' DAO object handles
Dim dbCurrent As daO.Database
Dim rs As daO.Recordset
lTotalRecsExported = 0
' Open the MDB
Set dbCurrent = OpenDatabase(MDBName, False, True)
' Open the recordset
Set rs = dbCurrent.OpenRecordset(Ta bleName, dbOpenDynaset, dbReadOnly)
' Move to the first record
rs.MoveFirst
' Loop while not at EOF
Do While Not rs.EOF
' Make sure there is data in this record.
lTemp = rs.Fields(OLEFieldName).Fi eldSize
If lTemp > 0 Then
' Size our arays
ReDim a(0 To lTemp - 1)
ReDim b(0 To lTemp - 1)
' Copy the contents of the OLE field to our byte array
a = rs.Fields(OLEFieldName).Va lue
' Make a copy
b = a
' Make a Call our function to extract the contents of the field
PackageFileName = ""
blRet = fGetContentsStream(a(), sExt, PackageFileName)
' Skip saving to disk if unsupported object type
' or fail on return
If blRet = True Then
' Increment counter
lTotalRecsExported = lTotalRecsExported + 1
' If a file was dragged from the Explorer window
' it will have a Package object Filename of NULL
' inserted by Shell.DLL
' Catch and give a temp file name
If sExt = "pak" Then
If Len(PackageFileName & vbNullString) < 3 Then
PackageFileName = rs.Fields(NameField).Value & "." & "bmp"
End If
' Package object
sl = SaveFolderName & "\" & PackageFileName
'for debugging "C:\sourcecode\OLEdatatest files\"
Else
' File Name derived from field user chose to "name" output files.
sl = SaveFolderName & "\" & rs.Fields(NameField).Value & "." & sExt
End If
' Write to disk
iFileHandle = FreeFile
Open sl For Binary Access Write As iFileHandle
Put iFileHandle, , a
Close iFileHandle
' Write original filed all debug
' iFileHandle = FreeFile
' Open sl & "DEBUG" For Binary Access Write As iFileHandle
' Put iFileHandle, , b
' Close iFileHandle
End If
' Jump here if the OLE field for this record was empty
End If
' Move to next record
rs.MoveNext
' Update our Status Label
Me.lblUpdate.Caption = "Exporting Record:" & rs.AbsolutePosition + 1 & _
" of " & rs.RecordCount
DoEvents
' Check our Flag
If blExporting = False Then
' Exit this loop
Exit Do
End If
Loop
If blExporting = False Then
' Update our Status Label
Me.lblUpdate.Caption = "Export Halted. " & lTotalRecsExported & " Records Exported!"
Else
' Update our Status Label
Me.lblUpdate.Caption = "Export Completed. " & lTotalRecsExported & " Records Exported!"
End If
EXIT_export:
Set rs = Nothing
dbCurrent.Close
Set dbCurrent = Nothing
' Free our instance of cDIBSection
FreeDS
' Release structured storage library
UnLoadLib
Exit Sub
' Cleanup
ERR_export:
Resume EXIT_export
End Sub
Here's the code
What I need to do is update a field called DocPath to reflect where the document has been saved, but I have no idea how to achieve this, can anyone help.
Code is from http://www.lebans.com/oletodisk.htm (Stephen Lebans) take a trip there it has some excellent tools.
Thank you in advance
Private Sub ExportOLE()
' Array of Bytes to hold the contents of the OLE field
Dim a() As Byte
' For copy of OLE field before processing. Use to debug
' and examine new/unsupported objects.
Dim b() As Byte
' Temp vars
Dim x As Long
Dim lTemp As Long
Dim sl As String
Dim blRet As Boolean
Dim lngRet As Long
Dim DocPath As String
' File extension of OLE object
Dim sExt As String
' Load our Structured Storage Library
' Let's see if the StrStorage.DLL is available.
blRet = LoadLib()
If blRet = False Then
' Cannot find StrStorage.dll file
Exit Sub
End If
' Create instance of cDIBSection in the modGetContentsStream module
'Dim ds As New cDIBSection
CreateDS
' Counter
Dim lTotalRecsExported As Long
On Error GoTo ERR_export
' This is an optional param we pass to fGetContentsStream.
' It will contain the original file name of the
' object when embedded as a Package.
Dim PackageFileName As String
' File number
Dim iFileHandle As Integer
' DAO object handles
Dim dbCurrent As daO.Database
Dim rs As daO.Recordset
lTotalRecsExported = 0
' Open the MDB
Set dbCurrent = OpenDatabase(MDBName, False, True)
' Open the recordset
Set rs = dbCurrent.OpenRecordset(Ta
' Move to the first record
rs.MoveFirst
' Loop while not at EOF
Do While Not rs.EOF
' Make sure there is data in this record.
lTemp = rs.Fields(OLEFieldName).Fi
If lTemp > 0 Then
' Size our arays
ReDim a(0 To lTemp - 1)
ReDim b(0 To lTemp - 1)
' Copy the contents of the OLE field to our byte array
a = rs.Fields(OLEFieldName).Va
' Make a copy
b = a
' Make a Call our function to extract the contents of the field
PackageFileName = ""
blRet = fGetContentsStream(a(), sExt, PackageFileName)
' Skip saving to disk if unsupported object type
' or fail on return
If blRet = True Then
' Increment counter
lTotalRecsExported = lTotalRecsExported + 1
' If a file was dragged from the Explorer window
' it will have a Package object Filename of NULL
' inserted by Shell.DLL
' Catch and give a temp file name
If sExt = "pak" Then
If Len(PackageFileName & vbNullString) < 3 Then
PackageFileName = rs.Fields(NameField).Value
End If
' Package object
sl = SaveFolderName & "\" & PackageFileName
'for debugging "C:\sourcecode\OLEdatatest
Else
' File Name derived from field user chose to "name" output files.
sl = SaveFolderName & "\" & rs.Fields(NameField).Value
End If
' Write to disk
iFileHandle = FreeFile
Open sl For Binary Access Write As iFileHandle
Put iFileHandle, , a
Close iFileHandle
' Write original filed all debug
' iFileHandle = FreeFile
' Open sl & "DEBUG" For Binary Access Write As iFileHandle
' Put iFileHandle, , b
' Close iFileHandle
End If
' Jump here if the OLE field for this record was empty
End If
' Move to next record
rs.MoveNext
' Update our Status Label
Me.lblUpdate.Caption = "Exporting Record:" & rs.AbsolutePosition + 1 & _
" of " & rs.RecordCount
DoEvents
' Check our Flag
If blExporting = False Then
' Exit this loop
Exit Do
End If
Loop
If blExporting = False Then
' Update our Status Label
Me.lblUpdate.Caption = "Export Halted. " & lTotalRecsExported & " Records Exported!"
Else
' Update our Status Label
Me.lblUpdate.Caption = "Export Completed. " & lTotalRecsExported & " Records Exported!"
End If
EXIT_export:
Set rs = Nothing
dbCurrent.Close
Set dbCurrent = Nothing
' Free our instance of cDIBSection
FreeDS
' Release structured storage library
UnLoadLib
Exit Sub
' Cleanup
ERR_export:
Resume EXIT_export
End Sub
If you're just inserting the path and filename on the fly then you already have that defined in your SL variable. Simply insert it into the field
If sExt = "pak" Then
If Len(PackageFileName & vbNullString) < 3 Then
PackageFileName = rs.Fields(NameField).Value & "." & "bmp"
End If
' Package object
sl = SaveFolderName & "\" & PackageFileName
'for debugging "C:\sourcecode\OLEdatatest files\"
Else
' File Name derived from field user chose to "name" output files.
sl = SaveFolderName & "\" & rs.Fields(NameField).Value & "." & sExt
End If
Something to this effect, while the recordset is open.
rs.edit
rs.fields("docpath").value = sl
rs.update
If sExt = "pak" Then
If Len(PackageFileName & vbNullString) < 3 Then
PackageFileName = rs.Fields(NameField).Value
End If
' Package object
sl = SaveFolderName & "\" & PackageFileName
'for debugging "C:\sourcecode\OLEdatatest
Else
' File Name derived from field user chose to "name" output files.
sl = SaveFolderName & "\" & rs.Fields(NameField).Value
End If
Something to this effect, while the recordset is open.
rs.edit
rs.fields("docpath").value
rs.update
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I knew I would have difficulty explaining, so excuse my poor description. I'll try to outline it a little better, but it's worth mentioning that I am struggling to understand the existing code let alone how best to modify it.
The existing code effectively exports ole objects out of my CostLetters Table to a specified Folder on the Hard Disk (C:\Program Files\CMITS\Shared\Documen ts). It uses the ID field (LetterID) as the filename when exporting the documents. This all works fine and dandy, saving me hours and hours.
At the point when it exports the ole object, I need the code to also update a currently blank field on the same row called DocPath with the path used to export the ole object. For example if the first row has a word document embedded, then it will be exported to C:\Program Files\CMITS\Shared\Documen ts\-122654 354.doc. I need the DocPath field to be updated just to read \Documents\-122654354.doc. I can then use the path to open the document from it's new location.
I hope this is clearer
Thanks
The existing code effectively exports ole objects out of my CostLetters Table to a specified Folder on the Hard Disk (C:\Program Files\CMITS\Shared\Documen
At the point when it exports the ole object, I need the code to also update a currently blank field on the same row called DocPath with the path used to export the ole object. For example if the first row has a word document embedded, then it will be exported to C:\Program Files\CMITS\Shared\Documen
I hope this is clearer
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you show us your version of the code that you're using and we can plug the edit in for you then you can cut and paste.
J
J
ASKER
Thanks Rick, I am almost there, the only problem I have now is that the DocPath field get's updated with the full path C:\Program Files\CMITS\Shared\Documen ts\documen tname. What I need to do is parse this so that all that is recorded is \Documents\documentname. Any suggestions?
Thanks
Martin
Thanks
Martin
I think a small alternation to this line....
rs.Fields("PathAndFileName ").Value = sl
will do the job. Try changing it to...
rs.Fields("PathAndFileName ").Value = Mid(sl, InStr(1, sl, "\Documents\"))
Good luck,
Rick
rs.Fields("PathAndFileName
will do the job. Try changing it to...
rs.Fields("PathAndFileName
Good luck,
Rick
ASKER
You guys are the business, many thanks the problem is solved and I am about to break free from the torture of embedded documents at long last!!!
I have split the points as both of you were saying the same thing, I hope this is OK and I thank you again.
Martin
I have split the points as both of you were saying the same thing, I hope this is OK and I thank you again.
Martin
Glad we could help.
Rick
Rick
' Write to disk
iFileHandle = FreeFile
Open sl For Binary Access Write As iFileHandle
Put iFileHandle, , a
Close iFileHandle
WriteSavedObjInfo(sl)