Using VBA to open and copy....

[BRUCE HAMILTON]

Is there a way, using VBA, to open an existing file, select a table in the file, copy the table, and close the file...without the user seeing anything?

I have a document set up as a template with a command button in an existing table. Clicking on the command button brings up a UserForm with several selections available. Clicking on one of the selections will....1)Open an existing file, 2)Navigate to the table in the file, 3) Select the table, 4) Copy the table, 5) Deselect the table, 6)Close the file, 7) Paste the copied table into a cell in the template. It works as is right now, but everything happens on screen, I would prefer to have it all happen "behind-the-scenes" so to speak so the user does not have to see anything happen except the copied table show up in the template table.

Any suggestions...help?

 

[Dawn Crosier]

Yes -

I do this all the time. I have a bookmark set in the destination document
which is where I paste it. The AutoNew macro is setup in the destination
document / template.
Let me know if you need more help.

***************Code Start***********************************

Sub AutoNew()

Dim appWord As Word.Application
Dim strLetter As String
Dim strDocsPath As String
Dim strTemplatePath As String
Dim strFileName As String
Dim strDate As String

Set appWord = GetObject(, "Word.Application")
strFileName = "LetterHeadTable.doc"
strDocsPath = DocsDir
strTemplatePath = TemplateDir
strLetter = strTemplatePath & strFileName

appWord.Documents.Open strLetter
ActiveDocument.Tables(1).Range.Select
Selection.Copy
ActiveDocument.Close
Selection.GoTo What:=wdGoToBookmark, Name:="letterhead"
Selection.Paste

Public Function TemplateDir() As String

On Error GoTo ErrorHandler

Set appWord = GetObject(, "Word.Application")
'TemplateDir = "http://xpmasters/templates/"
TemplateDir = appWord.Options.DefaultFilePath(wdWorkgroupTemplatesPath) &
"\"
ErrorHandlerExit:
Exit Function

ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume ErrorHandlerExit
End If
End Function

Public Function DocsDir() As String

On Error GoTo ErrorHandler

Set appWord = GetObject(, "Word.Application")
DocsDir = appWord.System.PrivateProfileString("", _

"HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell
Folders", _
"Personal") & "\"

ErrorHandlerExit:
Exit Function

ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume ErrorHandlerExit
End If
End Function
********************Code End****************************************

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.
Is there a way, using VBA, to open an existing file, select a table in the
file, copy the table, and close the file...without the user seeing anything?

I have a document set up as a template with a command button in an existing
table. Clicking on the command button brings up a UserForm with several
selections available. Clicking on one of the selections will....1)Open an
existing file, 2)Navigate to the table in the file, 3) Select the table, 4)
Copy the table, 5) Deselect the table, 6)Close the file, 7) Paste the copied
table into a cell in the template. It works as is right now, but everything
happens on screen, I would prefer to have it all happen "behind-the-scenes"
so to speak so the user does not have to see anything happen except the
copied table show up in the template table.

Any suggestions...help?

 

[BRUCE HAMILTON]

Let me decipher this and I will get back to you.

Thanx for the help.
Yes -

I do this all the time. I have a bookmark set in the destination document
which is where I paste it. The AutoNew macro is setup in the destination
document / template.
Let me know if you need more help.

***************Code Start***********************************

Sub AutoNew()

Dim appWord As Word.Application
Dim strLetter As String
Dim strDocsPath As String
Dim strTemplatePath As String
Dim strFileName As String
Dim strDate As String

Set appWord = GetObject(, "Word.Application")
strFileName = "LetterHeadTable.doc"
strDocsPath = DocsDir
strTemplatePath = TemplateDir
strLetter = strTemplatePath & strFileName

appWord.Documents.Open strLetter
ActiveDocument.Tables(1).Range.Select
Selection.Copy
ActiveDocument.Close
Selection.GoTo What:=wdGoToBookmark, Name:="letterhead"
Selection.Paste

Public Function TemplateDir() As String

On Error GoTo ErrorHandler

Set appWord = GetObject(, "Word.Application")
'TemplateDir = "http://xpmasters/templates/"
TemplateDir = appWord.Options.DefaultFilePath(wdWorkgroupTemplatesPath) &
"\"
ErrorHandlerExit:
Exit Function

ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume ErrorHandlerExit
End If
End Function

Public Function DocsDir() As String

On Error GoTo ErrorHandler

Set appWord = GetObject(, "Word.Application")
DocsDir = appWord.System.PrivateProfileString("", _

"HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell
Folders", _
"Personal") & "\"

ErrorHandlerExit:
Exit Function

ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume ErrorHandlerExit
End If
End Function
********************Code End****************************************

--
Dawn Crosier
"Education Lasts a Lifetime"

This message was sent to a newsgroup. Please post back to the newsgroup so
all may follow the thread.
Is there a way, using VBA, to open an existing file, select a table in the
file, copy the table, and close the file...without the user seeing anything?

I have a document set up as a template with a command button in an existing
table. Clicking on the command button brings up a UserForm with several
selections available. Clicking on one of the selections will....1)Open an
existing file, 2)Navigate to the table in the file, 3) Select the table, 4)
Copy the table, 5) Deselect the table, 6)Close the file, 7) Paste the copied
table into a cell in the template. It works as is right now, but everything
happens on screen, I would prefer to have it all happen "behind-the-scenes"
so to speak so the user does not have to see anything happen except the
copied table show up in the template table.

Any suggestions...help?

 

[Peter Hewett]

Hi BRUCE HAMILTON

If you are using Word 2000 or later just set the documents VIsible property to False when
you open the Document that contains the table you want to copy. I'd also set screen
updating to false as well:

Public Sub CopyTableFromDoc()
Dim docTable As Word.Document

' Open the document containing the table we want to copy
Application.ScreenUpdating = False
Set docTable = Documents.Open("c:\docs\table document.doc", _
ReadOnly:=True, Visible:=False)

' Copy the first table (note this overwrites the clipboard)
docTable.Tables(1).Range.Copy

' Close the document and tidy up
docTable.Close wdDoNotSaveChanges
Application.ScreenUpdating = True
End Sub

HTH + Cheers - Peter