Solved: Open Excel from Within Word

[markh1182]

Hi,

I have the following code that runs to open excel. I plan on using this so that I can open excel, update the excel spreadsheet with data from a sql database then close, so this can then be used in a mail merge.

However, when I run this code I get a warning message:

"The remote data (System) is not accessible. Do you want to start the application Excel?"

If you click Yes, this then opens Excel, but how do I get around it bringing up a warning?

Sub OpenExcelFile()
Dim Chan
Dim MyFile
MyFile = "H:\94\USERS\MIL\_Hincma\Dev\Word\Templates-CV Register\merge.csv"
Chan = DDEInitiate(App:="Excel", Topic:="System")
DDEExecute Channel:=Chan, Command:="[OPEN(" & Chr(34) _
& MyFile & Chr(34) & ")]"
DDETerminate Channel:=Chan
End Sub

[fionabolt]

Mark

Try this instead:
Sub OpenExcelFile()
Dim xlApp As Object
Dim MyFile As String
Rem set-up
MyFile = "H:\94\USERS\MIL\_Hincma\Dev\Word\Templates-CV Register\merge.csv"
' -- find out if excel is already open
If Tasks.Exists(Name:="Microsoft Excel") = False Then
' -- if not open then open it
Set xlApp = CreateObject("Excel.Application")
ElseIf Tasks.Exists(Name:="Microsoft Excel") = True Then
' -- Excel is is already open, just return the object
Set xlApp = GetObject(, "Excel.Application")
Else
' -- Excel could not be found
MsgBox "The Application Excel could not be opened."
' -- stop the routine
End
End If
' -- make sure its visible
xlApp.Application.Visible = True
' -- and open the workbook
xlApp.workbooks.Open MyFile
Set xlApp = Nothing
End Sub


Regards,
F

[fionabolt]

Sorry, should have put an error handler in there:

Sub OpenExcelFile()
Dim xlApp As Object
Dim MyFile As String
Rem set-up
MyFile = "H:\94\USERS\MIL\_Hincma\Dev\Word\Templates-CV Register\merge.csv"
' -- find out if excel is already open
If Tasks.Exists(Name:="Microsoft Excel") = False Then
' -- if not open then open it
Set xlApp = CreateObject("Excel.Application")
ElseIf Tasks.Exists(Name:="Microsoft Excel") = True Then
' -- Excel is is already open, just return the object
Set xlApp = GetObject(, "Excel.Application")
Else
' -- Excel could not be found
MsgBox "The Application Excel could not be opened."
' -- stop the routine
End
End If
' -- make sure its visible
xlApp.Application.Visible = True
' -- and open the workbook
On Error GoTo ErrHandler
xlApp.Workbooks.Open MyFile
Set xlApp = Nothing
Exit Sub
ErrHandler:
Select Case Err.Number
Case "1004"
MsgBox "Error number: " & Str(Err.Number) & vbCr & _
"The Workbook """ & MyFile & """ could not be found.", _
vbCritical, "Workbook could not be opened"
Case Else
MsgBox Str(Err.Number) & " " & Err.Description, vbCritical, "VBA Error"
End Select
Set xlApp = Nothing
End Sub

[markh1182]

that worked :)

Thanks for your help.

Mark

[RECrerar]

Hi, I have a similar problem to the one posted here.

I have a program in Word that converts a .doc file to a .txt file and saves both files, and opens an excel work book that contains macros for later analysis. The code is shown below

Sub WordDocOpen2()

' Remove the .doc part of the file name
a = Len(ActiveDocument.Name)
a = a - 4
DocName = Left(ActiveDocument.Name, a)

' Create a new Folder with the same name as the file
MkDir ("P:\FEMAP Programming\" & DocName)
' Open the new folder, make this the active folder
ChangeFileOpenDirectory "P:\FEMAP Programming\" & DocName
' Save the file both as a .doc file and as a .txt file
SaveAsNameDoc = DocName & ".doc"
SaveAsNameText = DocName & ".txt"
ActiveDocument.SaveAs FileName:=SaveAsNameDoc, FileFormat:=wdFormatDocument
ActiveDocument.SaveAs FileName:=SaveAsNameText, FileFormat:=wdFormatText

' Close the newly saved documents
ActiveDocument.Close

' Open and activate Excel
Set exl = CreateObject("Excel.Application")
exl.Workbooks.Open "P:\FEMAP Programming\Program Version 5.xls"
Call LoadFile

msg = "The Following Files Have Been Saved in " & CurDir & ":"
msg = msg & vbNewLine & vbNewLine
msg = msg & SaveAsNameDoc & vbNewLine
msg = msg & SaveAsNameText & vbNewLine & vbNewLine
msg = msg & "Run 'Import CBP File' and enter '" & DocName & "' when prompted, to perform the analysis"
msg = MsgBox(msg, vbInformation, "Document Conversion Complete")

exl.Visible = True
If Documents.Count = 0 Then Application.Quit

End Sub

From the excel workbook I then run the marco below to open the .doc file. (note DocName and FileNm are exactly the same, there is no reason they are labeled differently in the two programs, except I forgot what I originally called it)

Sub GetFile()

TRY_AGAIN:

FileNm = InputBox("Please Enter the Desired File Name", "Load File")

' Exit sub if no name is entered
If FileNm = "" Then Exit Sub

' Exit Sub if the file is already open
If WorkbookOpen(FileNm & ".xls") Then
Workbooks(FileNm & ".xls").Activate
Msg = "The requested file is already open" & vbNewLine
Msg = Msg & "Do you want to run the analysis now?"
Ans = MsgBox(Msg, vbYesNo, "Continue?")
If Ans = vbYes Then Call DrawCentreLine
Exit Sub
End If

'Don't Open the file if the file name is not recognised
On Error GoTo NOT_POSSIBLE

Workbooks.OpenText Filename:= _
("P:\FEMAP Programming\" & FileNm & "\" & FileNm & ".txt"), Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
ActiveWorkbook.SaveAs ("P:\FEMAP Programming\" & FileNm & "\" & FileNm & ".xls")
Ans = MsgBox("Do you want to run the analysis now?", vbYesNo, "Continue?")
If Ans = vbYes Then Call DrawCentreLine
Exit Sub

NOT_POSSIBLE:
'Reset Error Trapping
On Error GoTo 0
Ans = MsgBox("The Requested file was not recognised, Retry?", vbYesNo, "Error Occured")
If Ans = vbYes Then Resume TRY_AGAIN Else Exit Sub

End Sub

What I would like to be able to do is open the .txt file directly from Word. I can get the file to open but it brings up the option box for opening .txt files and I need the options to be selected automatically. Any Ideas?

[fionabolt]

This is the Word forum. Is your code in the Word template or in your workbook?

[RECrerar]

The first set of code I have writen is in word. The Second lot is in Excel but I would like to be able to write it in word, except I don't know how to. Is there a way to open a document in a .txt format in Excel from word?

[fionabolt]

Ah. OK.

You would have to test it, but I would imagine that in my previous code

xlApp.Workbooks.Open MyFile


could be replaced by :

xlApp.Workbooks.OpenText Filename:= _
("P:\FEMAP Programming\" & FileNm & "\" & FileNm & ".txt"), Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True


Regards,

[RECrerar]

I've tried that and it doesn't work, It wants all the variables seperately defined. Thanks