Link to home
Start Free TrialLog in
Avatar of karlos81
karlos81

asked on

How to import/export load and unload PICTURES/OLE Objects from Excel to Access

Problem:
1.      Loading excel data from excel file  SOLVED

SOLVED: open the workbook, read from each sheet the data in the columns and insert the data into the Access database table.

2.      Loading pictures from excel to Access / SQL database  not solved

NOT SOLVED  HELP NEEDED: read pictures/ole objects from the excel sheet and store them in the database. This could be achieved in three different ways:
A.      Read and save all pictures from the worksheet into Clipboard and then save them to a specified directory on the disk. Afterwards read the pictures from the predefined location and load them accordingly into the table, using a browse button to select the picture and a load button to actually load it.
B.      Use VBA native function xlsht.Shapes.Item(1).CopyPicture for example. However I have been unable to make it work after 5 days of intense programming. Help is more than welcomed.
C.      Convert the pictures/objects to Excel Charts (or chart background  correct me if I am wrong) and than save those on the HDD and use a browse and load button in Access to store them into the database (similar to solution A).

3.      Inserting loaded pictures from Access/SQL Database back into excel  not solved.

NOT SOLVED  HELP NEEDED: do the vice-versa of 2.). For each record in the database insert the picture back into the excel file, at the exact position & size as it was initially read.

My thanks will go to the many who will have their hand at solving this issue. There are dozens of posts on the web that have went unanswered on this precise issue.
1.	VBA code for button Load Data from Excel
 
Private Sub btnload_Click()
Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim myrec As DAO.Recordset
Set myrec = CurrentDb.OpenRecordset("reportfc")
Set xl = CreateObject("Excel.Application")
Set xlWrkBk = GetObject("C:/Data_Local_old/book1.xls")
Set xlsht = xlWrkBk.Worksheets(5)
myrec.AddNew
myrec.Fields("idrfc") = 1
myrec.Fields("idr") = 1
myrec.Fields("ido") = 1
myrec.Fields("idv") = 0
myrec.Fields("name") = xlsht.Cells(4, "A")
myrec.Fields("tehname") = xlsht.Cells(4, "B")
myrec.Fields("flag_activ") = 1
myrec.Fields("data") = "10.02.2009"
MsgBox xlsht.Shapes.Item(1).Name
'myrec.Fields("file") = xlsht.Shapes.Item("Picture 1")  this does not work&
myrec.Fields("nota") = "no comment!"
myrec.Update
myrec.Close
MsgBox ("Successfully loaded data from excel sheet")
End Sub
 
Those who wish to use my code to load similar formatted data should be aware to change .Worksheets(5) to .Worksheets(x) , where X is the actual number of the sheet in the workbook.

Open in new window

post1.bmp
post2.bmp
SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America 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
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
I asked for a sample xls as you have one with attached pics and in the format you wanted. I also noted no one else made an effort to look into this for you. Well I was trying to help and would of looked into it for you had I got your xls file but obviously you felt I was wasting your time. Not a problem, that is your opinion, we shall leave it at that. But good of you to post your solution, which helps ee and readers of ee in the long run.