将图片（gif / jpg / bmp）从Excel导出到ACCESS数据库（-fes [英] Exporting pictures (gif/jpg/bmp) from Excel to an ACCESS database (-fes
问题描述
我正在尝试开发一个例程，我将图片和文本从网站复制到Excel中，然后使用VBA将图片和文本导出到Access数据库的不同字段。
文本导入/导出工作正常。但是，我无法使用VBA从Excel中拉出与单个单元格关联的 图片（形状对象）。有没有办法做到这一点，然后将图片发送到ACCESS。
图片似乎以某种方式绑定到特定单元格，因为我可以将包含图片的列复制并粘贴到另一列。但是，如果我'清除'，图片仍然存在。电子表格中的内容或删除列。这些图片（OLEobjects？）因此被视为类似于图表;但是，我无法使用OLEobjects.select选择它们。
我可以通过获取总计数来识别单个图片（使用形状对象）使用ActiveSheet.Shapes.Count在工作表上的形状对象的数量。然后，我可以使用ActiveSheet.Shapes（n）.AlternativeText获取工作表上所有对象的AlternativeText名称，其中n是从1到ActiveSheet.Shapes.Count的范围。 因此，我可以 使用Web名称（为每张图片提供的文本字符串）识别并选择与每个单元相关联的图像，从而单独选择每个图像。
问题在于无法将图像带入VB变量，然后我可以将其传递给Access。 / font>
是否有一种简单的方法可以拉出这些可识别的Shape对象[例如 - ActiveSheet.Shape（n）]到VBA中的ole对象变量，我可以将其作为OLE图片对象传递给Access表中图片字段的各行。
例如，这个荒谬的例子是否有一组类似的命令：
将图片视为对象
XLPicture = ActiveSheet。形状（N）。???
[即 - 引用ActiveSheet.Shape（n）]
Rs.fields（"picture"）= XLpicture
< font face ="Times New Roman"size = 2>
（我知道上面显示的命令不起作用。但是，必须有一个类似的命令或一组命令来执行此操作）
感谢您的帮助
我现在发现这个问题的解决方案是将图片/图像复制到Excel中的剪贴板，然后使用ACCESS对象库中的DoCmd（Microsoft Access 11.0对象库）粘贴剪贴板的内容进入"图片" Access表中记录的组成部分。
执行此操作的代码如下所示：
设置MyData = ActiveSheet.Cells（4，ColNum）
DBtableName =" DVDs"
DoCmd.OpenTable DBtableName
< br> reTestLoop： t = t + 1
DoCmd.RunCommand acCmdRefresh
DoCmd.Requery
Application.Wait（Now + TimeValue（" 0：00：01"））
DoCmd。 RunCommand acCmdRecordsGoToLast
DoCmd。 GoToControl"ProgramName"
DoCmd.RunCommand acCmdCopy
工作表（RawDATASheet）.Paste目的地：= MyData
If Sheets（RawDATASheet）.Cells（4，ColNum）<> ProgramName然后 GoTo reTestLoop
'立即就绪，存储图片在表格中表格（RawDATASheet）.Shapes（PicNumUSE）.Copy
DoCmd.GoToControl" Picture"
DoCmd.RunCommand acCmdPaste
在上面的代码中，DoCmd用于打开和控制"DVD"中的字段。表。我只需打开表，转到最后一条记录（我转到DVD表中的最后一条记录，因为我之前使用DAO添加了包含DVD名称的新记录以及其他文本信息。）。然后我将图片（图像）从Excel复制到剪贴板并简单地将其粘贴（acCmdPaste）到相应记录的图片字段中。
这种方法效果很好，因为很多时候没有找到第一条记录的第一条记录时间我使用 DoCmd.RunCommand acCmdRecordsGoToLast转到最后一条记录。 一旦我使用DAO 从EXCEL输入包含文本数据的新记录，似乎需要一些时间才能在打开的ACCESS应用程序中更新表。
因此我必须插入一个循环（reTestLoop）以确保通过比较"程序名称"来选择正确的记录。在Access表的记录中使用DAO输入的内容。
我尝试了不同的方法来尝试解决这个问题（包括写入表单或查询），使用多个requery命令，并在使用Access Object库函数之前直接从DAO重新读取表中的数据。
不幸的是，由于DAO读取后更新表格所带来的延迟，以这种方式写入图片可能需要几秒钟才能生成一张图片。正如你所看到的，我已经尝试过REFRESHING以及REQUERYing表，但是这些似乎没有改变打开表所需的时间，因此它会找到正确的记录。我也尝试在循环中重复打开然后关闭表格，这也使现在有所不同。
有人可以帮助我找到更好的方法，我不必在代码中放置一个延迟循环，这样我就可以阅读和粘贴到相应的记录中 。
谢谢
I am trying to develop a routine where I copy pictures and text from websites into Excel and then use VBA to export the pictures and text into different fields of an Access database.
The text import/export works fine. However, i cannot pull the pictures (shape objects) associated with individual cells out of Excel using VBA. Is there any way to do this, and then send the pictures to ACCESS.
The pictures appear to be bound somehow to particular cells because I can copy and paste a column containing pictures to another column. However, the pictures remain if I 'Clear" the contents of, or delete the column from the spreadsheet. These pictures (OLEobjects?) are thus treated similar to charts; however, I cannot select them using OLEobjects.select.
I can identify the individual pictures (using Shape Objects) by getting a total count of the number of shape objects on the worksheet using ActiveSheet.Shapes.Count. I can then get the AlternativeText names of all Objects on the worksheets using ActiveSheet.Shapes(n).AlternativeText, where n is the range from 1 to ActiveSheet.Shapes.Count. As a result, I can use the Web Names (which are supplied for each picture as a text string) to identify and select the picture associated with each cell and thus select each picture individuall.
The problem resides in NOT being able to bring the picture into a VB variabile, which I can then pass to Access.
Is there a simple way to pull these identifiable Shape objects [e.g. - ActiveSheet.Shape(n)] into an ole object variable in VBA, which I can then pass as an OLE picture object to individual rows of a picture field in an Access table..
For example, is there a similar set of commands to this Ludicrous example:
Dim Picture as Object
XLPicture = ActiveSheet. Shape(n).???
[i.e. - to refer to ActiveSheet.Shape(n)]
Rs.fields("picture")= XLpicture
(I know that the commands shown above will NOT work.
BUT, there must be a similar command or set of commands to do this)
Thanks for your help
I have NOW found that the solution to this problem is to copy the picture/image to the clipboard in Excel and then use the DoCmd in the ACCESS object library (Microsoft Access 11.0 Object Library) to paste the contents of the clipboard into the "Picture" component of a record in an Access table.
The code to do this is shown below:
Set MyData = ActiveSheet.Cells(4, ColNum)
DBtableName = "DVDs"
DoCmd.OpenTable DBtableName
reTestLoop: t = t + 1
DoCmd.RunCommand acCmdRefresh
DoCmd.Requery
Application.Wait (Now + TimeValue("0:00:01"))DoCmd.RunCommand acCmdRecordsGoToLast
DoCmd.GoToControl "ProgramName"
DoCmd.RunCommand acCmdCopy
Worksheets(RawDATASheet).Paste Destination:=MyData
If Sheets(RawDATASheet).Cells(4, ColNum) <> ProgramName Then GoTo reTestLoop
'READY now, STORE Picture in Table
Sheets(RawDATASheet).Shapes(PicNumUSE).Copy
DoCmd.GoToControl "Picture"
DoCmd.RunCommand acCmdPaste
In the code above, the DoCmd is used to open and control fields in the "DVDs " Table. I simply open the table, go to the last record (I go to the last record in the DVDs table because I had previously used DAO to add a new record containing the name of the DVD as well as other text information.). I then copy the picture(image) from Excel to the clipboard AND simply Paste (acCmdPaste) it into the Picture field of the appropriate record.
This works well EXCEPT for the fact that many times the last record isn't found the first time i go to the last record using DoCmd.RunCommand acCmdRecordsGoToLast. It appears that it takes some time for the table to be updated in the open ACCESS application once I've entered the new record containing text data into the table from EXCEL using DAO.
I've thus have had to insert a Loop (reTestLoop) to make sure that the correct record has been selected by comparing the "Program Name" in the record of the Access table to what was entered using DAO.
I've tried different ways to try and get around this (including writing to a form or query), using multiple requery commands, and re-reading the data from the table directly from DAO prior to using the Access Object library functions.
Unfortunately, writing the pictures in this way can take many seconds for a single picture because of the delay associated with updating the table after the DAO read. As you can see, I've tried REFRESHING as well as REQUERYing the table, but these don't seem to change the time necessary to open the table so that it will find the correct record. I've also tried opening and then closing the table repeatedly in the loop, and this also make now difference.
Could someone help me find a better way, where I don't have to put a delaying loop in the code so that I can read and the paste into the appropriate record.
THANKS
这篇关于将图片（gif / jpg / bmp）从Excel导出到ACCESS数据库（-fes的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
