| Title: | Microsoft Excel User Hints and Kinks |
| Moderator: | FIEVEL::FILGATE |
| Created: | Thu May 27 1993 |
| Last Modified: | Fri Jun 06 1997 |
| Last Successful Update: | Fri Jun 06 1997 |
| Number of topics: | 483 |
| Total number of notes: | 1391 |
My problem is the following, I hope someone can help me with it.
(Excel version 7.0)
I want to invoke Excel from another program, and want to give a comma
separated textfile as a parameter which has to be imported in Excel.
(normally formatted into columns)
So in the command line I want to specify a file wich has to be imported
normally in Excel.
If I interactively import a textfile it's no problem. But to
invoke this from the commandline is more difficult.
I think I can't do it with macro's because the name of the textfile can
change. (the format is always the same however)
It would be a solution if I could make an Automacro and attach that to
a Excel workbook. Give this workbook as a parameter to Excel, and give
the text file as a second parameter which will be used by the macro.
But I don't know if this is possible and if it is, how to do it.
If I give 2 parameters to Excel it makes 2 worksheets of it, and puts
the text file in one cell in a second worksheet.
If anybody has a solution or any valuable tips, I would appreciate it
very much.
Thank you all for reading.
Guido
| T.R | Title | User | Personal Name | Date | Lines |
|---|---|---|---|---|---|
| 470.1 | One way forward... | OSEC::pervy.mco.dec.com::gilbertb | cyberpaddler | Thu Jan 30 1997 17:43 | 43 |
A trick that may work for you is to always name your text file with the
extension .CSV Excel is (usually) installed as the application associated
with this file type and will automatically fire up and import it. Try it
out by just double-clicking a .CSV file in Explorer.
You can then simply call the Win95 shell specifying your .CSV file as the
"program" to execute.
I used this technique in a VBA application after overcoming the fact that
the VBA help documentation lies about the built-in Shell function. To use
my trick from VBA you need to invoke the Windows ShellExecute function.
Here's the code I used, derived from the Microsoft Knowledge Base article
that admits the VBA defieicency but provides this work-round. It *should*
be reasonably generic.
Brian.
Declare Function ShellExecute Lib "shell32.dll" Alias " _
ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation _
As String, ByVal lpFile As String, ByVal lpParameters _
As String, ByVal lpDirectory As String, ByVal nShowCmd _
As Long) As Long
Global Const SW_SHOWNORMAL = 1
Function StartDoc(DocName As String)
' Executes an external program by specifying a document of a type
' with which it is associated.
' The VBA Shell function can't do this, despite what the Help file says!
Dim hWnd As Long
On Error GoTo StartDoc_Error
hWnd = 0
StartDoc = ShellExecute(hWnd, "Open" & Chr(0), DocName & Chr(0), "" &
Chr(0), "C:\" & Chr(0), SW_SHOWNORMAL)
Exit Function
StartDoc_Error:
MsgBox "Error reported by StartDoc: " & Err & " " & Error()
Exit Function
End Function
| |||||