Posts by homegrownandy

homegrownandy · Aug 20th 2018

Thats what ill do, Thanks.

homegrownandy · Aug 17th 2018

thanks for the reply, I dont believe I can do that.

Code

Sub ExcelDataToWord()






Dim objWord As Object
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch


lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row


    Set ws = ThisWorkbook.Sheets("RISKS")
    Set objWord = CreateObject("Word.Application")


    objWord.Visible = True


 
'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  ws.Range("A4" & ":H" & lngLastRow).Copy
 '------------browse---------------




    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
    
'--------------------defaulting folder------------------------
    With fileExplorer
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .ButtonName = "Select"
    .InitialView = msoFileDialogViewList
    .InitialFileName = "\\server\general\RAMS\RAM_RAMS"
    If Right(strName, 1) <> "\" Then
        strFolder = strFolder
    End If
    If .Show <> -1 Then
        Exit Sub
    Else
        strFolder = .SelectedItems(1)
    End If
End With


'--------------------defaulting folder------------------------
 


'------------browse---------------
  'open the word doc
      'objWord.Documents.Open "C:\Users\name\Desktop\RAMS AUTOMATION\Import table test.docx" 'change as required
        objWord.Documents.Open Filename:=strFolder


'pastes the value of cell at the bookmark
    With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable
    HeadingFormat = True
    End With


'Optimize Code
Set objWord = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True


'Clear The Clipboard
  Application.CutCopyMode = False
Exit Sub


Errorcatch:
     Debug.Assert False
     MsgBox err.Description
     ' This is temporary, if you leave it in it will go into an endless loop so do not forget to remove
     Resume


End Sub

Display More

so its this line:

Code

With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable

I can look at other paste options but this is pasting into a table and other options have formatting issues. What do you think?

Andy.

Update:

If you don't think there's a "one line" solution here I could add code that copies this data to a new sheet first and then carries on with the rest of the code.

homegrownandy · Aug 17th 2018

"thisworkbook.ExcelDataToWord" Works. Took me a while to find it. Thanks anyway

homegrownandy · Aug 17th 2018

Im running this code from a microsoft access application with the intent to run an existing macro within the opened document.

The document does open but i get the error:

run-time error '1004': Cannot run the macro "ExcelDataToWord". The macro may not be available in this workbook or all macros may be disabled.

Note: this macro is contained in the "this workbook" section. I'm probbaly not referencing it properly, Any help appreciated.

Code

Private Sub Command21_Click()


Dim xl As Object
 
'Step 1:  Start Excel, then open the target workbook.
   Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("\\SERVER\general\Documents\!Management\VBA_Templates_do_not_modify\HAZARDS.xlsm")
 
'Step 2:  Make Excel visible
   xl.Visible = True
 
'Step 3:  Run the target macro
  [COLOR=#FF0000] xl.Run "ExcelDataToWord"[/COLOR]
 
'Step 4:  Close and save the workbook, then close Excel
   xl.ActiveWorkbook.Close (True)
    xl.Quit
 
'Step 5:  Memory Clean up.
   Set xl = Nothing




End Sub

Display More

Thanks,
Andy

homegrownandy · Aug 17th 2018

Im using this line of code in some VBA to copy rows with data in them:

Code

lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row

The problem is that these cells, although blank, contain formulas. How can I stop these being copied?

Thanks in advance,

Andy

homegrownandy · Aug 17th 2018

It is a requirement for this code to run to have the following option enabled in word:

smart cut and paste enabled.
Adjust table formatting and alignment on paste enabled.

However, the table im pasting into then goes half way off the screen.

Currently trouble shooting, Ill make a new post if i need help as its a different issue. But if anyone else has a similar problem it may help.

homegrownandy · Aug 16th 2018

It has to be paste append, the information is from access but I have a lot of formatting errors when pasting from access. When using excel this DID work.

Thanks for your suggestions,

The data to be pasted in had one less column or a blank column. This was causing the error.

I wouldnt have had a clue where to start without your input so again, Thanks a lot.

homegrownandy · Aug 16th 2018

Sorry and thanks for the tip there.

you were correct it is on this line:

Code

With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Screenshot_2.png","data-attachmentid":1207067}[/ATTACH]

Maybe the document isnt "active" when im in excel? I dont know why it was working previously though.

homegrownandy · Aug 16th 2018

Set objWord = CreateObject("Word.Application")

I thinkits on this line. The bookmark does exist ive checked. And the document does open now after your suggestion. Its a bit strange it's "working" to open it and then saying it can't

homegrownandy · Aug 16th 2018

Please note it doesnt mention no document is open now.

Also note the code worked before i implimented the file selection dialog. If i manually assign the path for the file there are no problems.

Andy.

homegrownandy · Aug 16th 2018

Thank you for the reply,
I have annotated the slashes out of the code which does now open the document. Thank you for that. I still get the error: this command is not available.

Code

Sub ExcelDataToWord()






Dim objWord As Object
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch


lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row


    Set ws = ThisWorkbook.Sheets("RISKS")
    Set objWord = CreateObject("Word.Application")


    objWord.Visible = True


 
'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  ws.Range("A4" & ":H" & lngLastRow).Copy
 '------------browse---------------




    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
    
'--------------------defaulting folder------------------------
    With fileExplorer
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .ButtonName = "Select"
    .InitialView = msoFileDialogViewList
    .InitialFileName = "\\server\general\RAMS\RAM_RAMS"
    If Right(strName, 1) <> "\" Then
        strFolder = strFolder '& "\"
    End If
    If .Show <> -1 Then
        Exit Sub
    Else
        strFolder = .SelectedItems(1) '& "\"
    End If
End With
Debug.Print strFolder
Debug.Print fileExplorer
'--------------------defaulting folder------------------------
 


'------------browse---------------
  'open the word doc
      'objWord.Documents.Open "C:\Users\name\Desktop\RAMS AUTOMATION\Import table test.docx" 'change as required
        objWord.Documents.Open Filename:=strFolder


'pastes the value of cell at the bookmark
    With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable
    End With


'Optimize Code
Set objWord = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True


'Clear The Clipboard
  Application.CutCopyMode = False
Exit Sub


Errorcatch:
MsgBox err.Description


End Sub

Display More

homegrownandy · Aug 16th 2018

UPDATE:

I've changed:

Code

objWord.Documents.Open Filename:=fileExplorer

to:

Code

objWord.Documents.Open Filename:=strFolder

This is correct I believe, However I now get the error: this command is not available because no document is open

homegrownandy · Aug 16th 2018

I have this code working in parts but not together. I can pen a word document using the top part and I can insert to a table using the bottom part (but not using file select I have to define the document path.)

When I put these together I get type mismatch. Any ideas why?

Type mismatch happens once i select the word document.

Code

Sub ExcelDataToWord()




Dim objWord As Object
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch


lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row


    Set ws = ThisWorkbook.Sheets("RISKS")
    Set objWord = CreateObject("Word.Application")


    objWord.Visible = True


 
'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  ws.Range("A4" & ":H" & lngLastRow).Copy
 '------------browse---------------




    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
    
'--------------------defaulting folder------------------------
    With fileExplorer
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .ButtonName = "Select"
    .InitialView = msoFileDialogViewList
    .InitialFileName = "\\server\general\RAMS\RAM_RAMS"
    If Right(strName, 1) <> "\" Then
        strFolder = strFolder & "\"
    End If
    If .Show <> -1 Then
        Exit Sub
    Else
        strFolder = .SelectedItems(1) & "\"
    End If
End With


'--------------------defaulting folder------------------------
 


'------------browse---------------


        objWord.Documents.Open Filename:=fileExplorer


'pastes the value of cell at the bookmark
    With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable
    End With


'Optimize Code
Set objWord = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True


'Clear The Clipboard
  Application.CutCopyMode = False
Exit Sub


Errorcatch:
MsgBox err.Description


End Sub

Display More

homegrownandy · Jul 19th 2018

this is something i've already asked yesterday on the access forums:

http://www.accessforums.net/showthread.php?t=72952

I have the code working and it does paste how i want but there are formatting issues.

I need "Paste options: merge" in VBA code. If that is possible please let me know. Thank you.

Andy

homegrownandy · Apr 25th 2018

This site has been a pain to use recently for me. Anyway I used your sample last week but i couldn't thank you at the time.

Thanks a lot for your work, its perfect.

homegrownandy · Apr 18th 2018

I will pick this up again tomorrow when I have some more time to understand looping (bit rusty)

Cheers!

homegrownandy · Apr 18th 2018

Appreciate the code there, Would it be a problem for you to make this loop for me? Not having much luck with it!

homegrownandy · Apr 18th 2018

I'm trying to adapt some code I found online to no avail:

Code

Sub MergingCells()
For i = 2 To Range("c3:c200").End(xlDown).Row
Sheets("ADMIN").Hyperlinks.Add anchor:=Cells(i, 3), Address:=Cells(i, 2).Value, TextToDisplay:=Cells(i, 1).Value
Next i
End Sub

the sheet is called admin.
c3:c200 = the "friendly name"
D3:d200 = URL
e3:e200 = Output (none currently)

When clicked it looks like tis doing something (loading symbol) but nothing changes. Is there anything obviously wrong here?

Andy

homegrownandy · Apr 18th 2018

I'm using the following function: =HYPERLINK(M7, N7)

this will take a link in M7 ad give it a friendly name from N7. That's great but I want to remove M7 and N7 and leave a text hyperlink in place of this formula.

I've seen suggestions saying to paste special and use HTML. This option is not available (been looking into this and I see no solution)

Could anyone recommend a way to achieve this?

Thanks in advance.

homegrownandy · Dec 6th 2017

I know what i want to achieve, I probably know how to do most of it myself too. Im just after some design advice before I begin. This is for a profit loss sheet for trading crypto currency.

On the final design I want a summary page that will show % profit/loss for each coin, then at the end a total. Possible in the future to link to the API for live data but historical will be ok for now.

I imagine (unless told otherwise) all coins would have to be kept on a seperate page/sheet? Prehaps some VBA that moves the coin to the right page on import, or creates it if needed.

Each "trade" listed will have a unique reference number. This should be checked on import to avoid duplicates. As they are moved, the qualtity on any order that is "buy" should have a negatiive value (basically add - before the number) so the figure is calculated correctly.

Im just wondering if someone could offer some feedback and possibly help with the code.

Andy

Posts by homegrownandy

Dont paste blank cells.

Dont paste blank cells.

run-time error '1004': Cannot run the macro (SOLVED)

run-time error '1004': Cannot run the macro (SOLVED)

Dont paste blank cells.

use FileDialog and remember selection (type mismatch)

use FileDialog and remember selection (type mismatch)

use FileDialog and remember selection (type mismatch)

use FileDialog and remember selection (type mismatch)

use FileDialog and remember selection (type mismatch)

use FileDialog and remember selection (type mismatch)

use FileDialog and remember selection (type mismatch)

use FileDialog and remember selection (type mismatch)

Paste into word merge

Paste Hyperlink function result.

Paste Hyperlink function result.

Paste Hyperlink function result.

Paste Hyperlink function result.

Paste Hyperlink function result.

Advice on csv import