Un-protecting Word Doc by Excel VBA for fillin and upload data to workbook automatically

SqnLdrMona

New Member
Joined
Oct 8, 2022
Messages
4
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
I've a workbook with VBA to send data to a workbook which is stored in a network shared drive. I now prompt the users to re-send the data by using a MsgBox, and another feature of the Sub is to fill ta Word Doc and a PDF for record purposes. The Word Doc will be protected after filling.

I now want to,
1. to protect the word doc to prevent users to edit it before VBA sending data to it
2. make it to wait until the the workbook is closed and upload the data automatically; and
3. confirm the data have been uploaded

Would the big Bros here give me some ideas or update my code I posted below. Thank you.
xxx
_______________
Sub DataToHHDHL_Referral()

'Declare Variables for This Automation

Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim sh As Worksheet

'Start Word and add a new document
Set wd = New Word.Application

'Set worksheet where table is avaialble
Set sh = ThisWorkbook.Sheets("Input")

If WorksheetFunction.CountA(Worksheets("Input").Range("B2,b4,b6,b8")) < 4 Then
MsgBox "Required info not filled"
Exit Sub
Else
End If

'Opening the word template where bookmarks have been added
Set wdDoc = wd.Documents.Open(ThisWorkbook.Path & "\HHDHL_Referral.docx")

wd.Visible = False

wd.Selection.GoTo What:=wdGoToBookmark, Name:="HotelAdd"
wd.Selection.TypeText Text:=sh.Range("E11").Value

'..........

'Clear the Bookmarks from this file

On Error Resume Next

wdDoc.Bookmarks("Picker").Delete

'...............

'wdDoc.SaveAs2 ("D:\YCCP\temp\word\" & Range("B10").Value & ".docx")
wdDoc.SaveAs2 ("C:\Users\mohqtf429\Desktop\HelpDeskLogTest\UserLog\" & Range("B10").Value & ".docx") 'save word copy to user desktop folder

Application.ScreenUpdating = False

Response = MsgBox("Do you need a printout??", vbYesNo)

If Response = vbYes Then
wdDoc.ActiveWindow.PrintOut , Copies:=1, Collate:=True
'Application.Wait Now = TimeValue("00:00:15")
Else
'Ret = Unprotect: "1234"
'MsgBox "File is Closed"
End If

'wdDoc.ExportAsFixedFormat "D:\YCCP\temp\pdf\" & Range("B10").Value & ".pdf", wdExportFormatPDF, True
wdDoc.ExportAsFixedFormat "Z:\HQTF MO Room\HCST Data\Data Testing\PDFLog\" & Range("B10").Value & ".pdf", wdExportFormatPDF, True



wdDoc.Protect Password:="1234", NoReset:=False, Type:=wdAllowOnlyFormFields


wdDoc.Close

Set wdDoc = Nothing

wd.Quit
Set wd = Nothing

MsgBox "The info have not been saved"

End Sub

Sub TransferDataToMasterBookn()

Dim wbMaster As Workbook
Dim wbLocal As Workbook
Dim MasterNextRow As Long
Dim Cell As Range
Dim Ret

If WorksheetFunction.CountA(Worksheets("Input").Range("B2,b4,b6,b8")) < 4 Then
MsgBox "Required info not filled"
Exit Sub
Else
End If

Response = MsgBox("Have you checked the information?")
If Response = vbYes Then
Application.ScreenUpdating = False
Ret = IsWorkBookOpen("Z:\HQTF MO Room\HCST Data\Data Testing\Masterdata.xlsx")
If Ret = True Then
MsgBox "Master file is being used! "
'Application.Wait Now = TimeValue("00:00:15")
Exit Sub
Else
'Ret = Unprotect: "1234"
'MsgBox "File is Closed"
End If

Application.ScreenUpdating = False

Set wbLocal = ThisWorkbook
Set wbMaster = Workbooks.Open("Z:\HQTF MO Room\HCST Data\Data Testing\Masterdata.xlsx")

Sheets("Data").Unprotect Password:="1234"


'----------------------

MasterNextRow = wbMaster.Worksheets("data").Range("A" & wbMaster.Worksheets("data").Rows.Count).End(xlUp).Offset(1).Row

'----------------------------------------

wbMaster.Worksheets("Data").Cells(MasterNextRow, 1).Value = wbLocal.Worksheets("Input").Range("B4").Text
wbMaster.Worksheets("Data").Cells(MasterNextRow, 2).Value = wbLocal.Worksheets("Input").Range("B6").Value

'-----------------------------------
Sheets("Data").Protect Password:="1234"
wbMaster.Close True

'--------------------------------------'-----------------------------------
'--------------------------------------

Sheets("Input").Unprotect Password:="1234"

wbLocal.Worksheets("Input").Range("B6").Value = ""
wbLocal.Worksheets("Input").Range("B8").Value = ""
'.............

Sheets("Input").Protect Password:="1234"

Application.ScreenUpdating = True

MsgBox "Data have been uploaded."
Else
Response = MsgBox("Upload Cancelled", cbYesNo)
End If

End Sub


Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0

Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top