
Access
Lista de campos en Word usando el acceso
VBA
Código detrás de la forma: F_Menu_WordListFields
Llame a AWord_WriteFieldList_2NewDoc_S4P
'cbf: f_MENU_WordListFields '*************** Code Start *************************************************** ' Purpose : List Field information for ActiveDocument in Word ' to a new Word document using Microsoft Access ' Author : crystal (strive4peace) ' Code List: ' This tool: ' LICENSE : ' You may freely use and share this code, but not sell it. ' Keep attribution. Mark your changes. Use at your own risk. '-------------------------------------------------------------------------------- '~~~~~~~~~~ Report Private Sub cmd_ListFields_Click() '250404 Call aWord_WriteFieldList_2NewDoc_s4p End Sub '*************** Code End ******************************************************
mod_aword_writefieldlist_2newdoc_s4p
módulo estándar
Procedimientos de llamadas en
- Bas_LeaseWait
- mod_getuniquefilename_s4p
Option Compare Database Option Explicit 'module: mod_aWord_WriteFieldList_2NewDoc_s4p '*************** Code Start *************************************************** ' Purpose : List Field information for ActiveDocument in Word ' to a new Word document using Microsoft Access ' Author : crystal (strive4peace) ' Code List: ' This tool: ' LICENSE : ' You may freely use and share this code, but not sell it. ' Keep attribution. Mark your changes. Use at your own risk. '-------------------------------------------------------------------------------- ' module variables '-------------------------------------------------------------------------------- 'early binding 'Public moWord As Word.Application 'Public moDoc As Word.Document 'late binding Public moWord As Object Public moDoc As Object Dim mStartTimer As Single _ ,mDtmStart As Date '------------------------------------------------------------------------------- ' aWord_WriteFieldList_2NewDoc_s4p '------------------------------------------------------------------------------- Public Sub aWord_WriteFieldList_2NewDoc_s4p() 'load field info for ActiveDocument to an array 'AND write fieldlist to a table in a new document 'returns number of fields '230626 s4p, 240720, 250106 pbWatch, 250404 'CLICK HERE ' PRESS F5 to RUN! 'CALL ' Word_Set_ActiveDocument ' WriteFieldArray_2NewWordDoc_s4p ' StartTime ' EndTime ' bas_PleaseWait ' PleaseWaitShow ' PleaseWaitClose ' PleaseWaitMsg ' GetElapsedTime On Error GoTo Proc_Err '----- dimension object variables Dim oField As Object 'Word.Field '----- dimension scalar variables Dim sFieldCode As String _ ,sField As String _ ,sMsg As String _ ,sPathFile As String _ ,vParameter As Variant _ ,nCountField As Long _ ,nOrdr As Long _ ,nStart As Long _ ,nEnd As Long _ ,nPage As Long _ ,nPages As Long _ ,nPageLastField As Long _ ,iPos As Integer Dim asField() As String 'array with field information '-------------------------------------- StartTime, PleaseWait sMsg = "aWord_WriteFieldList_2NewDoc_s4p " _ & " record field information for the active document in Word" Call StartTime(sMsg) Call PleaseWaitShow(sMsg) '-------------------------------------- '----- check moDoc 'don't skip message If Not Word_Set_ActiveDocument Then GoTo Proc_Exit End If nCountField = moDoc.Fields.Count If nCountField = 0 Then MsgBox "No fields in " & moDoc.Name _ ,, "no fields to list" GoTo Proc_Exit End If ReDim asField(1 To nCountField,1 To 12) 'not all these are reported '1. Order '2. Field '3. Parameter(s) '4. Switch(es) '5. Field Code '6. Result '7. Index '8. Kind '9. start position '10. end position '11. number of characters '12. page number 'get each field in document nOrdr = 0 'initialize variables nPage = 0 nPageLastField = -1 'number of pages in document nPages = moDoc.Content.Information(3) 'wdActiveEndPageNumber 'loop each field in document, write to array For Each oField In moDoc.Fields nOrdr = nOrdr + 1 asField(nOrdr,1) = nOrdr With oField sFieldCode = .Code nPage = .Result.Information(3) 'wdActiveEndPageNumber asField(nOrdr,5) = sFieldCode & " " & Format(nOrdr, "0000") 'for sorting asField(nOrdr,6) = .Result asField(nOrdr,7) = .Index asField(nOrdr,8) = .Kind asField(nOrdr,9) = Format(.Result.Start, "#,##0") asField(nOrdr,10) = Format(.Result.End, "#,##0") asField(nOrdr,11) = Format(.Result.Characters.Count, "#,##0") asField(nOrdr,12) = nPage End With iPos = InStr(sFieldCode, "\") If iPos > 0 Then asField(nOrdr,4) = Mid(sFieldCode,iPos) 'Switch(es) sField = Trim(Left(sFieldCode,iPos - 1)) Else asField(nOrdr,4) = "" 'Switch(es) sField = Trim(sFieldCode) End If iPos = InStr(sField, " ") If iPos > 0 Then asField(nOrdr,2) = Left(sField,iPos - 1) 'field asField(nOrdr,3) = Trim(Mid(sField,iPos + 1)) 'parameter(s) Else asField(nOrdr,2) = sField 'field End If 'keep track of when page changes ' , update PleaseWait form If nPage <> nPageLastField Then Call PleaseWaitMsg( _ sMsg & vbCrLf & vbCrLf & " page " & nPage _ & " of " & nPages _ ) nPageLastField = nPage End If Next oField 'done loading fields into array sMsg = "Done loading array, create Word document with results" Call PleaseWaitMsg(sMsg) sPathFile = WriteFieldArray_2NewWordDoc_s4p(asField,nCountField,True) 'vbOK=1,vbYesNo=4 sMsg = "Documented " _ & Format(nCountField, "#,##0") _ & " fields" _ & vbCrLf & GetElapsedTime() Debug.Print sMsg Call PleaseWaitClose sMsg = "open " & sPathFile & "?" If MsgBox(sMsg,vbYesNo, "done") = vbYes Then 'open document in new instance Application.FollowHyperlink sPathFile End If Proc_Exit: On Error Resume Next Call EndTime 'only in error handler? Call PleaseWaitClose Set oField = Nothing Set moDoc = Nothing Set moWord = Nothing On Error GoTo 0 Exit Sub Proc_Err: MsgBox Err.Description _ ,, "ERROR " & Err.Number _ & " aWord_WriteFieldList_2NewDoc_s4p" Resume Proc_Exit Resume End Sub '------------------------------------------------------------------------------- ' WriteFieldArray_2NewWordDoc_s4p '------------------------------------------------------------------------------- Private Function WriteFieldArray_2NewWordDoc_s4p( _ pasField() As String _ ,pnCountFields As Long _ ,Optional pbWatch As Boolean = True _ ) As String 'return name of sPathFile created 's4p 240626, 240404, 5 'CALL ' GetUniqueFilename_s4p ' WordTableBorders_s4p ' PleaseWaitMsg 'PARAMETERS ' pasField is array of field information ' pnCountFields is number of fields ' pbShowDone is TRUE if you want to be prompted to open the document ' pbWatch is TRUE if you want to watch as Word document is being created '----- dimension object variables ' Dim oSummaryDoc As Word.Document _ ,oRange As Word.Range _ ,oRangeStart As Word.Range _ ,oRangeHeader As Word.Range _ ,oTable As Word.Table Dim oSummaryDoc As Object _ ,oRange As Object _ ,oRangeStart As Object _ ,oRangeHeader As Object _ ,oTable As Object '----- dimension scalar variables Dim nRows As Long _ ,nCols As Long _ ,nRow As Long _ ,iPos As Integer _ ,i As Integer _ ,iButton As Integer _ ,sPathFile As String _ ,sText As String _ ,sMsg As String '----- moDoc 'get path\file for ListFields summary document sPathFile = moDoc.FullName iPos = InStrRev(sPathFile, "\") sPathFile = Left(sPathFile,iPos) _ & "ListFields_" _ & Mid(sPathFile,iPos + 1) 'call GetUniqueFilename_s4p sPathFile = GetUniqueFilename_s4p( _ sPathFile, "yymmdd_hhnn") '----- RETURN VALUE WriteFieldArray_2NewWordDoc_s4p = sPathFile Set oSummaryDoc = moWord.Documents.Add Call PleaseWaitMsg( "creating Summary document: " _ & vbCrLf & vbCrLf & sPathFile) 'moWord.Visible = True If pbWatch Then moDoc.Activate 'make it Landscape, set margins With oSummaryDoc.PageSetup .Orientation = 1 'wdOrientLandscape .TopMargin = CInt(0.5 * 72) 'InchesToPoints .BottomMargin = CInt(0.5 * 72) 'InchesToPoints .LeftMargin = CInt(0.6 * 72) 'InchesToPoints .RightMargin = CInt(0.5 * 72) 'InchesToPoints End With 'save document with constructed name oSummaryDoc.SaveAs sPathFile sPathFile = oSummaryDoc.FullName '----- UPDATE RETURN VALUE (file extension) WriteFieldArray_2NewWordDoc_s4p = sPathFile 'get ready to write Set oRangeStart = oSummaryDoc.Content oRangeStart.Collapse 0 'wdCollapseEnd 'Heading 1, Heading 2 With oSummaryDoc.Content 'Title .InsertAfter "Field List, " _ & Format(Now(), "yymmdd hh:nn ") 'style as Heading 1 .Paragraphs(oSummaryDoc.Paragraphs.Count).Style _ = oSummaryDoc.Styles( "Heading 1") .InsertParagraphAfter .InsertAfter "source file: " & moDoc.FullName .InsertParagraphAfter .InsertAfter "this documentation file: " & oSummaryDoc.FullName .InsertParagraphAfter 'style as Heading 3 .Paragraphs(.Paragraphs.Count - 1).Style _ = oSummaryDoc.Styles( "Heading 3") .InsertParagraphAfter sText = UBound(pasField) - LBound(pasField) + 1 & " fields" .InsertAfter sText If pbWatch Then ' change selection 'goto the end of the document 'wdStory=6, wdMove=0 moWord.Selection.EndKey unit:=6,Extend:=0 End If End With 'oSummaryDoc.Content With oSummaryDoc 'range for table, put at end Set oRange = .Content oRange.Collapse Direction:=0 'wdCollapseEnd 'insert table nCols = 8 'number of columns 'NumRows: number of fields + 1 for header row Set oTable = .Tables.Add( _ Range:=oRange _ ,NumRows:=pnCountFields + 1 _ ,NumColumns:=nCols _ ) End With 'oSummaryDoc 'customize and write table With oTable 'dont allow rows to break .Rows.AllowBreakAcrossPages = False 'Vertical Alignment for each cell is Top ' 0=wdCellAlignVerticalTop .Range.Cells.VerticalAlignment = 0 'heading row .Rows(1).HeadingFormat = True .Cell(1,1).Range.Text = "Page" .Cell(1,2).Range.Text = "Ordr" .Cell(1,3).Range.Text = "Start" .Cell(1,4).Range.Text = "Len" .Cell(1,5).Range.Text = "Field" .Cell(1,6).Range.Text = "Parameter(s)" .Cell(1,7).Range.Text = "Switch(es)" .Cell(1,8).Range.Text = "Result" For i = 1 To 4 .Cell(1,i).Range.ParagraphFormat.Alignment = 2 'wdAlignParagraphRight Next i 'format nRow = 1 'header row just written 'Write data from array 'loop through properties and write values for moDoc For nRow = LBound(pasField) + 1 To UBound(pasField) + 1 If pbWatch Then .Cell(nRow,1).select 'so user can see what's being written End If .Cell(nRow,1).Range.Text = pasField(nRow - 1,12) 'page .Cell(nRow,2).Range.Text = pasField(nRow - 1,1) 'order .Cell(nRow,3).Range.Text = pasField(nRow - 1,9) 'Start .Cell(nRow,4).Range.Text = pasField(nRow - 1,11) 'Length .Cell(nRow,5).Range.Text = pasField(nRow - 1,2) 'Field .Cell(nRow,6).Range.Text = pasField(nRow - 1,3) 'Parameter(s) sText = pasField(nRow - 1,4) 'Switch(es) If sText <> "" Then sText = "\" & Replace(Mid(sText,2), "\",Chr(10) & "\") End If .Cell(nRow,7).Range.Text = sText .Cell(nRow,8).Range.Text = pasField(nRow - 1,6) 'Result For i = 1 To 4 .Cell(nRow,i).Range.ParagraphFormat.Alignment = 2 'wdAlignParagraphRight Next i 'format Next nRow 'best-fit columns .Columns.AutoFit 'format With .Range.ParagraphFormat .SpaceAfter = 0 .SpaceBefore = 0 End With 'ParagraphFormat 'keep with at least one row With .Cell(1,1).Range.ParagraphFormat .KeepTogether = True .KeepWithNext = True End With End With 'oTable 'add table borders Call PleaseWaitMsg( "add table borders") Call WordTableBorders_s4p(oTable) With oSummaryDoc.Content 'go to end of document -- another way, using Content .MoveEnd unit:=6 'wdStory 'write how many fields were found .InsertAfter "** " _ & Format(pnCountFields, "#,##0") _ & " Field" _ & IIf(nRows <> 1, "s ", "") & " listed" 'add final line break .InsertParagraphAfter 'goto top .movestart unit:=6 End With 'oSummaryDoc.Content Write_Header: 'add header to oSummaryDoc Set oRangeHeader = oSummaryDoc.Sections(1).Headers(1).Range ' With oRangeHeader ' oSummaryDoc.Sections(1).Headers(1).Range 'wdFieldEmpty = -1 .Fields.Add Range:=.Characters.Last _ ,Type:=-1 _ ,Text:= "STYLEREF ""Heading 1"" " _ ,PreserveFormatting:=False 'then a TAB and text on right ' oRangeHeader .InsertAfter Chr(9) & "strive4peace, " _ & Format(Date, "d-mmm-yy, ddd") _ & ", page " 'then PAGE/NUMPAGES '33=wdFieldPage 'wdFieldEmpty = -1 .Fields.Add Range:=.Characters.Last _ ,Type:=-1 _ ,Text:= "PAGE" ' _ ,PreserveFormatting:=True .InsertAfter Text:= "/" '26=wdFieldNumPages .Fields.Add Range:=.Characters.Last _ ,Type:=-1 _ ,Text:= "NUMPAGES" ' _ ,PreserveFormatting:=True 'add border line below With .Borders(-3) 'wdBorderBottom =-3 .LineStyle = 1 'wdLineStyleSingle=1 .LineWidth = 8 'wdLineWidth100pt=8 .Color = RGB(75,75,75) 'dark gray End With With .ParagraphFormat 'clear current tabstops .TabStops.ClearAll 'set right-aligned Tab Stop at 7.5 inches '72 points/inch '2=wdAlignTabRight '0=wdTabLeaderSpaces .TabStops.Add _ Position:=10 * 72 _ ,Alignment:=2 _ ,Leader:=0 '0=Left, 1=Center, 2=Right .Alignment = 0 '0=wdAlignParagraphLeft 'space after paragraph = 6 points .SpaceAfter = 6 End With .Fields.Update End With 'header 'save and close With oSummaryDoc .Save .Close End With sMsg = "Done listing Fields to a new Word document" _ & Format(Now, ", yymmdd hh:nn") _ & vbCrLf & " " & sPathFile Debug.Print "******** " & sMsg Call PleaseWaitMsg(sMsg) 'release object variables On Error Resume Next Set oRange = Nothing Set oRangeStart = Nothing Set oRangeHeader = Nothing Set oTable = Nothing Set oSummaryDoc = Nothing On Error GoTo 0 End Function '------------------------------------------------------------------------------- ' Word_Set_ActiveDocument '------------------------------------------------------------------------------- Private Sub ReleaseWord() Set moWord = Nothing Set moDoc = Nothing End Sub Private Function Word_Set_ActiveDocument( _ Optional psErrorText As String = "" _ ) As Boolean '240117 s4p set goApp and moWord, 250212, 221 Set moWord = Nothing Set moDoc = Nothing Word_Set_ActiveDocument = False 'Initialize Word On Error Resume Next Set moWord = GetObject(, "Word.Application") On Error GoTo Proc_Err If moWord Is Nothing Then MsgBox "Word isn't open" _ & vbCrLf & vbCrLf & psErrorText _ ,, "Can't get Word Object" Exit Function End If 'still here -- see if any docs open With moWord If Not .Documents.Count > 0 Then MsgBox "No ActiveDocument in Word" _ & vbCrLf & vbCrLf & psErrorText _ ,, "Can't get Word ActiveDocument" Exit Function End If Set moDoc = .ActiveDocument Word_Set_ActiveDocument = True End With Proc_Exit: On Error Resume Next Exit Function Proc_Err: MsgBox Err.Description _ ,, "ERROR " & Err.Number _ & " Word_Set _ActiveDocument" Resume Proc_Exit Resume End Function '------------------------------------------------------------------------------- ' StartTime, EndTime '------------------------------------------------------------------------------- Private Sub StartTime(Optional pMsg) On Error Resume Next mStartTimer = Timer() mDtmStart = Now() DoCmd.Hourglass True Debug.Print "--- START-------------" _ & pMsg & " ----- " & CStr(mDtmStart) End Sub Private Sub EndTime() '...240110 'call in Exit code. ReportElapsedTime may have beenused to show message On Error Resume Next DoCmd.Hourglass False SysCmd acSysCmdClearStatus Debug.Print "End " & Format(Now(), "h:nn") & " ----" End Sub '------------------------------------------------------------------------------- ' WordTableBorders_s4p '------------------------------------------------------------------------------- 'Object is Word.Table Private Sub WordTableBorders_s4p(oTable As Object _ ,Optional pbHeaderRow As Boolean = True _ ) 's4p 170811, 240818 pbHeaderRow Dim i As Integer With oTable For i = 1 To 6 'wdBorderTop =-1 'wdBorderLeft = -2 'wdBorderBottom =-3 'wdBorderRight= -4 'wdBorderHorizontal = -5 'wdBorderVertical = -6 With .Borders(-i) .LineStyle = 1 'wdLineStyleSingle=1 .LineWidth = 8 'wdLineWidth100pt=8. wdLineWidth150pt=12 .Color = RGB(200,200,200) 'medium-light gray End With Next i End With 'mark heading row If pbHeaderRow <> False Then 'True With oTable.Rows(1) 'Heading Row .HeadingFormat = True 'Shading for header row .Shading.BackgroundPatternColor = RGB(232,232,232) 'change main borders to black for first row For i = 1 To 4 With .Borders(-i) .Color = 0 'wdColorBlack = 0 End With Next i End With 'first row End If 'Not used: ' 'wdLineStyleNone = 0 ' .Borders(-7).LineStyle = 0 'wdBorderDiagonalDown =-7 ' .Borders(-8).LineStyle = 0 'wdBorderDiagonalUp =-8 End Sub '------------------------------------------------------------------------------- ' GetElapsedTime '------------------------------------------------------------------------------- Private Function GetElapsedTime() As String 'strive4peace 250404 On Error Resume Next Dim sMsg As String _ ,nEndTime As Date _ ,dbSeconds As Double nEndTime = Now() dbSeconds = Timer - mStartTimer If dbSeconds < 0 Then dbSeconds = Timer + (24 * 60 * 60) - mStartTimer End If If dbSeconds > 60 * 60 Then sMsg = Format(dbSeconds / 60 / 60, "#,###.##") & " hours" ElseIf dbSeconds > 60 Then sMsg = Format(dbSeconds / 60, "#,###.##") & " minutes" Else sMsg = Format(dbSeconds, "#,###.##") & " seconds" End If sMsg = "Start Time: " _ & Format(mDtmStart, "hh:nn:ss") _ & vbCrLf _ & " End Time: " & Format(nEndTime, "hh:nn:ss") _ & vbCrLf _ & " Elapsed Time: " & sMsg GetElapsedTime = sMsg End Function '*************** Code End ******************************************************
Bas_LeaseWait
Código auxiliar para formulario de favor
'module name: bas_PleaseWait ' 5-17-08, 241104 '*************** Code Start *************************************************** ' Purpose : Helper code for PleaseWait form ' Author : crystal (strive4peace) ' website : https:\\msaccessgurus.com ' LICENSE : ' You may freely use and share this code, but not sell it. ' Keep attribution. Mark your changes. Use at your own risk. '-------------------------------------------------------------------------------- '------------------ open the PleaseWait form Public Sub PleaseWaitShow( _ Optional pMsg As String = "PleaseWait...") DoCmd.OpenForm "f_PleaseWait" Forms!f_PleaseWait.Label_Msg.Caption = pMsg Forms!f_PleaseWait.Repaint End Sub ' '------------------ close the PleaseWait form Public Sub PleaseWaitClose() If CurrentProject.AllForms( "f_PleaseWait").IsLoaded Then DoCmd.Close acForm, "f_PleaseWait",acSaveNo End If End Sub ' '------------------ change the PleaseWait message Public Sub PleaseWaitMsg( _ Optional pMsg As String = "PleaseWait...") On Error Resume Next Forms!f_PleaseWait.Label_Msg.Caption = pMsg Forms!f_PleaseWait.Repaint End Sub '*************** Code End *****************************************************
mod_getuniquefilename_s4p
código para fx_getuniquefileName.htm
‘El código se generó con colores utilizando el complemento de código de color gratuito para el acceso
Goto top