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).CopyP icture 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.
post2.bmp
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).CopyP
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.
post1.bmppost2.bmp
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.