vba to word help please.

[mirinjawbro]

Hello

Currently have a macro working that opens word. Pastes a range from excel and makes the margins narrow. It just will not work to have after spacing set to 0.

Can anyone help please?

Let's assume we copy a1 to f10. Open word. Paste. Narrow margins and after and before spacing set to 0

Thanks

 

[mirinjawbro]

Forgot to add ctrl c ctrl v pasted without spaces. Macro pasting adds doubles spaces.

 

[mirinjawbro]

Anyone? It's driving me mad

 

[Macropod]

I can't imaging how you expect anyone to provide specific advice when you've not posted the problem code. As for:

Currently have a macro working that opens word. Pastes a range from excel and makes the margins narrow. It just will not work to have after spacing set to 0.

Precisely what doesn't work?

 

[mirinjawbro]

Sorry I should be more clear

I have data from excel that I want to paste into a new word document. This needs to have 0 spacing and narrow margins

Thats all I have trouble with. I'm just looking for some code to do that

So copy a1 to b6 from excel
Open new word
Paste into word with narrow margins and 0 spacing

Thanks

 

[Macropod]

What you're describing is pretty basic, but vague. I have no idea what you consider 'narrow margins'.

Try something based on:

Sub SendRangeToDoc()
'Note: This code requires a reference to the Word Object Library,
' set via Tools|References in the VBA Editor.
Dim wdApp As New Word.Application, WdDoc As Word.Document
ActiveWorkbook.Sheets(1).Range("A1:B6").Copy
wdApp.Visible = True
Set WdDoc = wdApp.documents.Add
With WdDoc.Range
  .Paste
  With .ParagraphFormat
    .SpaceBefore = 0
    .SpaceAfter = 0
  End With
  With .PageSetup
    .TopMargin = wdApp.InchesToPoints(0.25)
    .BottomMargin = wdApp.InchesToPoints(0.25)
    .LeftMargin = wdApp.InchesToPoints(0.25)
    .RightMargin = wdApp.InchesToPoints(0.25)
  End With
End With
Set WdDoc = Nothing: Set wdApp = Nothing
End Sub

Most of the above code (almost everything after .Paste) wouldn't be needed if you actually used a Word template with the required layout & formatting specifications...

 

[mirinjawbro]

I did try making my own word template. But when pasting it changed the spacing back to 10. Very annoying

I will try the above code and let you know thIanks again

Margins are for print options

 

[mirinjawbro]

Thanks that worked perfectly.

Next step I have problem with is saving the word document using cell a1 As the name. I need it to open a directory with the save as dialog boxo

Lastly. Any books or courses anyone can recommend? I really wanna learn this myself.

Thanks

 

[Macropod]

It would be helpful if you said up-front what the full scope is of what you want to do. Your piecemeal approach just creates more work. Try:

Sub SendRangeToDoc()
'Note: This code requires a reference to the Word Object Library,
' set via Tools|References in the VBA Editor.
Dim wdApp As New Word.Application, WdDoc As Word.Document
ActiveWorkbook.Sheets(1).Range("A1:B6").Copy
wdApp.Visible = True
Set WdDoc = wdApp.documents.Add
With WdDoc
  With .Range
    .Paste
    With .ParagraphFormat
      .SpaceBefore = 0
      .SpaceAfter = 0
    End With
    With .PageSetup
      .TopMargin = wdApp.InchesToPoints(0.25)
      .BottomMargin = wdApp.InchesToPoints(0.25)
      .LeftMargin = wdApp.InchesToPoints(0.25)
      .RightMargin = wdApp.InchesToPoints(0.25)
    End With
  End With
  .SaveAs2 Filename:="C:\Users" & Environ("UserName") & _
    "\Documents\MyFolder\" & _
    Split(.Tables(1).Cell(1, 1).Range.Text)(0) & ".docx", _
    FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
  .Close
End With
'wdApp.Quit
Set WdDoc = Nothing: Set wdApp = Nothing
End Sub

As coded, the macro saves the file to a folder named 'MyFolder' in the user's Documents folder. Change the path to suit your actual requirements. As you will see when you run the code, there's no need to open the SaveAs dialogue box if you know the filename & path to use.

Once you have it working correctly, you can delete/comment-out the line 'wdApp.Visible = True' and un-comment the line 'wdApp.Quit'.

 

[mirinjawbro]

Thanks so much. Will give this a go

And yes I agree. I didn't realise I wanted all of this before.Thanks