copying specific info from a cell in Excel to Word

[Blanchetdb]

I need to copy a date (the current date) from a cell in Excel to a document in Word. I am able to do so but the date appears in the following format 2023-01-18 and I need it to appear as January 18, 2023

the following does what I need it to do but once I protect the sheet (with cell A17 and B17 both unlocked) it no longer works .... I get a debug message with the line (.Range("B17").NumberFormat = "mmmm dd yyyy") highlighted in yellow.

Dim Cell As Range

With ActiveSheet
.Range ("A17").Copy .Range("B17")
.Range("B17").NumberFormat = "mmmm dd yyyy"
For Each Cell In Range("B17")
Cell.Value = Cell.Text
Next Cell
End With

If I unlock the sheet, the date appears within the Word document as needed using the following vba:

Dim wdApp As Word.Application
Dim theString As String
Dim theObject As Object

Set wdApp = New Word.Application
Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

With wdApp
.Visible = True
.Activate

.Documents.Add "M:\HR\CFIA Transfer Inventory\Builder Templates\Indeterminate_Appointments_EN_Template.DOCX"

Sheet3.Activate

Dim Cell As Range

With ActiveSheet
.Range ("A17").Copy .Range("B17")
.Range("B17").NumberFormat = "mmmm dd yyyy"
For Each Cell In Range("B17")
Cell.Value = Cell.Text
Next Cell
End With

theString = Range("B17").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
.Selection.Goto what:=-1, Name:="Date"
.Selection.Paste 'paste from the clipboard to the Word Doc.

Can someone please provide me with assistance in regarding the date moving over to the Word document in the proper format (January 18, 2023) while the Excel sheet is locked? ...

thank you.

 

[mcomp72]

If the date is in cell A17 of your Excel sheet, you don't need to copy it to B17. You can just read the value of cell A17 into a string variable and set the formatting.

Dim TheDate As String

TheDate = ActiveSheet.Range("A17").Value

TheDate = Format(TheDate, "mmmm dd, yyyy")

'This line will show the value of TheDate in the Immediate window, in case you want to see it.  But it is not necessary.
Debug.Print TheDate

Now, you should be able to do this:

theObject.SetText TheDate

I believe the above will work even if the sheet is locked, since you are only reading from cell A17, not writing anything to it.

 

[DanteAmor]

moving over to the Word document in the proper format (January 18, 2023)

Try this:

Sub date_in_word()
  Dim wdApp As Word.Application
  Dim theString As String
  Dim theObject As Object

  theString = WorksheetFunction.Proper(Format(Sheet3.Range("B17").Value, "Mmmm dd, yyyy")) 'cast to string

  Set wdApp = New Word.Application
  Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
  
  With wdApp
    .Visible = True
    .Activate
    .Documents.Add "M:\HR\CFIA Transfer Inventory\Builder Templates\Indeterminate_Appointments_EN_Template.DOCX"
  
    theObject.SetText theString
    theObject.PutInClipboard 'copy from cell B2 to clipboard.
    .Selection.Goto what:=-1, Name:="Date"
    .Selection.Paste 'paste from the clipboard to the Word Doc.
  End With
End Sub

 

[Blanchetdb]

If the date is in cell A17 of your Excel sheet, you don't need to copy it to B17. You can just read the value of cell A17 into a string variable and set the formatting.

Dim TheDate As String

TheDate = ActiveSheet.Range("A17").Value

TheDate = Format(TheDate, "mmmm dd, yyyy")

'This line will show the value of TheDate in the Immediate window, in case you want to see it.  But it is not necessary.
Debug.Print TheDate

Now, you should be able to do this:

theObject.SetText TheDate

I believe the above will work even if the sheet is locked, since you are only reading from cell A17, not writing anything to it.

thank you for responding but it doesn't work when the sheet is protected. When the sheet is unprotected, I get the correct format (January 19, 2023) but once protected (2023-01-19).

 

[Blanchetdb]

Try this:

Sub date_in_word()
  Dim wdApp As Word.Application
  Dim theString As String
  Dim theObject As Object

  theString = WorksheetFunction.Proper(Format(Sheet3.Range("B17").Value, "Mmmm dd, yyyy")) 'cast to string

  Set wdApp = New Word.Application
  Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 
  With wdApp
    .Visible = True
    .Activate
    .Documents.Add "M:\HR\CFIA Transfer Inventory\Builder Templates\Indeterminate_Appointments_EN_Template.DOCX"
 
    theObject.SetText theString
    theObject.PutInClipboard 'copy from cell B2 to clipboard.
    .Selection.Goto what:=-1, Name:="Date"
    .Selection.Paste 'paste from the clipboard to the Word Doc.
  End With
End Sub

thank you for responding but it doesn't work when the sheet is protected. When the sheet is unprotected, I get the correct format (January 19, 2023) but once protected (2023-01-19).

 

[DanteAmor]

thank you for responding but it doesn't work when the sheet is protected. When the sheet is unprotected, I get the correct format (January 19, 2023) but once protected (2023-01-19).

Are you sure you tried the code from post #3?
It works for me if the sheet is protected.

So you unprotect the sheet and protect it again, try this:

Sub date_in_word()
  Dim wdApp As Word.Application
  Dim theString As String
  Dim theObject As Object

  Sheet3.Unprotect "abc"  'Change "abc" to your password or "" if you don't have a password
 
  theString = WorksheetFunction.Proper(Format(Sheet3.Range("B17").Value, "Mmmm dd, yyyy")) 'cast to string

  Set wdApp = New Word.Application
  Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 
  With wdApp
    .Visible = True
    .Activate
    .Documents.Add "M:\HR\CFIA Transfer Inventory\Builder Templates\Indeterminate_Appointments_EN_Template.DOCX"
 
    theObject.SetText theString
    theObject.PutInClipboard 'copy from cell B2 to clipboard.
    .Selection.Goto what:=-1, Name:="Date"
    .Selection.Paste 'paste from the clipboard to the Word Doc.
  End With
 
  Sheet3.Protect "abc"  'Change "abc" to your password or "" if you don't have a password
 
End Sub

 

[Blanchetdb]

Are you sure you tried the code from post #3?
It works for me if the sheet is protected.

So you unprotect the sheet and protect it again, try this:

Sub date_in_word()
  Dim wdApp As Word.Application
  Dim theString As String
  Dim theObject As Object

  Sheet3.Unprotect "abc"  'Change "abc" to your password or "" if you don't have a password
 
  theString = WorksheetFunction.Proper(Format(Sheet3.Range("B17").Value, "Mmmm dd, yyyy")) 'cast to string

  Set wdApp = New Word.Application
  Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 
  With wdApp
    .Visible = True
    .Activate
    .Documents.Add "M:\HR\CFIA Transfer Inventory\Builder Templates\Indeterminate_Appointments_EN_Template.DOCX"
 
    theObject.SetText theString
    theObject.PutInClipboard 'copy from cell B2 to clipboard.
    .Selection.Goto what:=-1, Name:="Date"
    .Selection.Paste 'paste from the clipboard to the Word Doc.
  End With
 
  Sheet3.Protect "abc"  'Change "abc" to your password or "" if you don't have a password
 
End Sub

that works ..... I knew that was an option but I was hoping to avoid entering the actual password within the application ... thank you

 

[Fluff]

that works

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.