- 帖子
- 1
- 主題
- 1
- 精華
- 0
- 積分
- 2
- 點名
- 0
- 作業系統
- w7
- 軟體版本
- 2007
- 閱讀權限
- 10
- 性別
- 男
- 註冊時間
- 2014-10-5
- 最後登錄
- 2015-2-8
|
如何在用word讀取excel指定欄位的值
目前寫了一個標籤程式
因為有個資想利用excel的值
當作變數來當做word另存新檔的密碼
我在excel內寫入密碼認證的自訂表單
只要密碼正確就可以查看客戶資料否則
相關資料是隱藏的無法看到客戶資料
我也在程式內寫入更改密碼的表單
密碼更改成功就會在F1寫入密碼
我要怎們利用word取得excel
儲存格F1的值
以下是excel更新資料巨集我把
認證寫在裡面了所以執行不需要輸入密碼
Application.ScreenUpdating = False
Windows("住址標籤套印格式(開啟自動完成).xlsm").Visible = True
ActiveSheet.Unprotect Password:="9527"
Cells.Select
Range("H1").Activate
Selection.EntireColumn.Hidden = False
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlUp
Dim abc As String
abc = ThisWorkbook.Path & "\"
Workbooks.OpenText _
Filename:=abc & "資料庫.txt", _
DataType:=xlDelimited, _
Comma:=True
.
.
.
.
處理資料
Columns("A:G").Select
Selection.EntireColumn.Hidden = True
Range("H1").Select
ActiveSheet.Protect Password:="9527"
ActiveWorkbook.Save
Set wo = CreateObject("word.application")
wo.Visible = True
wo.documents.Open ThisWorkbook.Path & "\住址標籤套印格式(開啟自動完成).docm"
Windows("住址標籤套印格式(開啟自動完成).xlsm").Close SaveChanges = False
EXCEL巨集↑執行完後自動開啟word
以下是我在word嘗試寫入的vba 結果都是執行失敗
Ps以下程式碼會開啟我寫的excel活頁簿
-------------------------------------------------------------------------------
Dim ex As Object
Set ex = CreateObject("excel.application")
'wo = ThisDocument.Path & "\住址標籤套印格式(開啟自動完成).xlsm"
Set wo = ex.Workbooks.Open(ThisDocument.Path & "\住址標籤套印格式(開啟自動完成).xlsm")
' "='[B.xls]Sheet1'!$A$1"
' With wo = Sheet1
'Dim ex As Object
'Set ex = CreateObject("Excel.Sheet")
'End With
以下是開啟word後自動執行但是沒有加密
---------------------------------------------------------------------------
Application.ScreenUpdating = False
abc = ThisDocument.Path & "\"
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
abc & "住址標籤套印格式(開啟自動完成).xlsm", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=abc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Lock" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.Execute Pause:=True
ActiveDocument.SaveAs FileName:= _
abc & "住址標籤套印(完成).docx", FileFormat:= _
wdFormatXMLDocument, LockComments:=False, Password:=(d), AddToRecentFiles _
:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False |
|
發表於 2015-1-13 23:43
| 
