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.
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.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"
'myrec.Fields("file") = xlsht.Shapes.Item("Picture 1") this does not work&
myrec.Fields("nota") = "no comment!"
MsgBox ("Successfully loaded data from excel sheet")
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.