標題: 如何在用word讀取excel指定欄位的值 [打印本頁]

---

作者: a7133756    時間: 2015-1-13 23:43     標題: 如何在用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

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)