頁:
[1]
他是用路徑連結，不適用插入圖片，導致傳給人EXCEL都顯示不出圖片，如何改?
本帖最後由 alien677501 於 2017-11-6 06:03 PM 編輯Option Explicit
Function getLastUsedRow(ws As Worksheet) As Long
Dim lastUsedRow As Long: lastUsedRow = 1
On Error Resume Next
lastUsedRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error GoTo 0
getLastUsedRow = lastUsedRow
End Function
Sub 插入產品圖片()
Application.ScreenUpdating = False
Dim i As Long
Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
' --------------------------------------------------------------------------------------------
' ----------------------------- VARIABLES FOR ACTIVE SHEET ! ---------------------------------
' --------------------------------------------------------------------------------------------
Dim wsData As Worksheet: Set wsData = ActiveSheet
Dim wsDataStartingRowAnswer As String ' Answer user gave for what is the starting row for the processing !
Dim wsDataStartingRow As Long ' Starting row !
Dim wsDataEndingRow As Long: wsDataEndingRow = getLastUsedRow(wsData) ' Global ending row on the whole sheet !
Dim wsDataProductNumberColumn As String
Dim wsDataProductPictureColumn As String
Dim wsDataProductNumber As String ' Current product number !
Dim wsDataProductPicturePath As String ' Current product picture file system path !
' --------------------------------------------------------------------------------------------
' --------------------------------------------------------------------------------------------
' ----------------------------- VARIABLES FOR FILE SYSTEM ! ----------------------------------
' --------------------------------------------------------------------------------------------
Dim path As String: path = ThisWorkbook.path
Dim picturesFolderName As String: picturesFolderName = "64x64"
'Dim picturesFolderPath As String: picturesFolderPath = path + Application.PathSeparator + picturesFolderName
Dim picturesFolderPath As String: picturesFolderPath = "M:\" + Application.PathSeparator + picturesFolderName
Dim importPicture As Object
' --------------------------------------------------------------------------------------------
' --------------------------------------------------------------------------------------------
' ------- CHECKING IF PICTURES FOLDER EXISTS IN THE SAME DIRECTORY AS CURRENT FILE ! ---------
' --------------------------------------------------------------------------------------------
If Not fso.FolderExists(picturesFolderPath) Then
' MsgBox "Folder named: " + picturesFolderName + " must be in the same directory as this file, please create it and run macro again!"
MsgBox "放置圖片的檔案夾找不到, 請確定有這個資料夾" + picturesFolderName + " "
Exit Sub
End If
' --------------------------------------------------------------------------------------------
' --------------------------------------------------------------------------------------------
' ------------------ ASKING FOR USER INPUT AND CHECKING ITS VALIDITY ! -----------------------
' --------------------------------------------------------------------------------------------
' Ask the user "What is Product number column?" !
' wsDataProductNumberColumn = InputBox("Please enter column letter where product numbers are stored" + vbCrLf + "For example: A", "Product number column")
wsDataProductNumberColumn = InputBox("我將從檔案夾 'M:\64x64' 裡面, 尋找並加入圖片" + vbCrLf + "請問產品編號在哪一欄?" + vbCrLf + "例如: A", "產品欄")
' Check if user inputted anything in product number column !
If wsDataProductNumberColumn = "" Then
' MsgBox "Product number column must be provided, please enter it and run macro again!"
MsgBox "必須輸入產品欄位才能置入圖片啦"
Exit Sub
End If
' Ask the user "What is the starting row?" !
' wsDataStartingRowAnswer = InputBox("Please enter starting row?" + vbCrLf + "For example: 12", "Starting row")
wsDataStartingRowAnswer = InputBox("從哪一列開始置入圖片?" + vbCrLf + "例如: 2", "開始列")
' Check if user provided correct starting run number !
If Not IsNumeric(wsDataStartingRowAnswer) Then
' MsgBox "Starting row must be provided and in correct format, please enter starting row number and run macro again!"
MsgBox "開始列只能輸入數字, 請重新執行巨集"
Exit Sub
End If
' Convert user provided answer to whole number (long number) !
wsDataStartingRow = CLng(wsDataStartingRowAnswer)
' Check if provided answer for product number column is an actual column letter !
wsDataProductNumberColumn = Trim(wsDataProductNumberColumn)
wsDataProductNumberColumn = UCase(wsDataProductNumberColumn)
If Len(wsDataProductNumberColumn) >...<div class='locked'><em>瀏覽完整內容，請先 <a href='member.php?mod=register'>註冊</a> 或 <a href='javascript:;' onclick="lsSubmit()">登入會員</a></em></div><div></div> 想請問為什麼要用”插入圖片”方式？
放在路徑，不是不對，而是在這個程式碼的目的上，很明顯她容許不同的產品項目，可以帶入多個圖檔。所以這段excel script, 很明顯是可擴充的。也就是，把excel想像成一個應用程式。
如果要改用”插入圖片”，等同於放棄掉了把excel當成一個應用程式，而只是一個文件編輯器。
舉實例：
以她的例子來說，她的每一列的資料，將來如果是透過ＨＴＴＰ取得伺服器資料，然後更新在ＥＸＣＥＬ中，那圖檔問題，也只要同時透過ＨＴＴＰ下載放到她原本指定的目錄下。
如果你改成插入圖片，那，將來透過ＨＴＴＰ取得伺服器資料，就沒辦法取得圖檔，因為你的ＥＸＣＥＬ檔案沒有插入那個圖檔，等於你要開ＥＸＣＥＬ把圖檔插入。那既然要這動作，就變成乾脆回歸到把ＥＸＣＥＬ當文字編輯器用就好，要多一筆資料，手動ＫＥＹ（反正也順便要插入圖檔）...<div class='locked'><em>瀏覽完整內容，請先 <a href='member.php?mod=register'>註冊</a> 或 <a href='javascript:;' onclick="lsSubmit()">登入會員</a></em></div>
頁:
[1]