Open File within a macro

[Rob]

I would like to know the code (to put within some other code), which will
run the Open file dialogue, & pointing to the desktop, so that a text file
can be selected.
(Then, I need that selected text file opened so that the macro will do some
action on that file and revert back to the workbook for mor action.)
I found something in help, which opens the dialogue box, but it does not
open the file, just brings up a message box that tells me the file I've
selected.

Furthermore, is there a way for that code to point to any desktop no matter
where it is located in the Windows Explorer tree? (As I plan to use the
workbook on various machines.)

Rob

 

[Leith Ross]

Hello Rob,

It will require using API (Applications Programming Interface) calls.
Bob Phillips created an easy macro that wraps the API calls in a class
module. Here is the link...

http://groups.google.com/group/microsoft.public.excel.programming/msg/f8e66f9e6f73cc4

SIncerely,
Leith Ross

 

[Nick Hodge]

Rob

You can use the GetOpenFileName method of the application object. This
method, as you've found, does not open the file, but returns a string with
the file name, you then use this to open the file (untested)

Sub GetTextFile()
Dim FileToOpen As String
ChDir ("C:\Documents and Settings\NickH\Desktop")
FileToOpen = Application.GetOpenFilename("Text Files (*.txt),*.txt")
Workbooks.Open (FileToOpen)
End Sub

Finding the desktop is another challenge as there could be multiple profiles
on each machine and each one of these has a 'desktop', how do you select the
correct one for your user? That one needs more thought or you need to allow
your user to select desktop and then check they have and error if they
don't.

Remember in the method, you can set the title of the dialog so that it says
something like "Open File From Desktop"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Guest]

As this is coded:

Sub GetData()
ChDir "C:\Documents and Settings\Owner\Desktop"
Workbooks.OpenText Filename:="x.txt", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(10, 1),
Array(20, 1) _
, Array(39, 1)), TrailingMinusNumbers:=True

Windows("x.txt").Activate
Call RetainData
Call LabelData
End Sub

It opens a text file on my desktop with a given number of columns (adapting
code from the macro recorder. (ignore the calls to the other two subs)

Just use an inputbox for your path and filename.

 

[Rob]

Thanks Leith,

I'm not that clued up to use something so complicated. I did copy that code
into a blank workbook but an error message I don't understand came up. I
might try some of the other newsgroup suggestions and work on this if the
others don't work.

Rob

 

[Rob]

Thanks Nick,

Had a quick test and seems to go OK on it's own. Hopefully putting it with
the Help offering will give me whatt I need.

I think the desktop issue may be too much for me to handle and so I'll
probably be content with the code you have submitted.

Rob

 

[Rob]

Thanks "Gary''s Student".

When I run this I get an error message stating that the Named argument
TrailingMinusNumbers not found.

What do I do with that, please? Do I need to stick that in a dim statement
somehow?

Rob

 

[Dave Peterson]

The TrailingMinusNumbers is an option that was added in xl2002.

You can delete that portion ", TrailingMinusNumbers:=True" and try it once more.

 

[Rob]

Thanks Dave,

However, I think I've missunderstood this procedure. I was hoping to obtain
the code to run the Open file dialogue pointing to any desktop, then to
allow the user to make a selection which would then import the text file
selected to a spreadsheet.

Rob

 

[Dave Peterson]

Record a macro when you parse your input file. You'll need that to lay out each
field.

Then you can merge your recorded code into this sample:

Option Explicit
Sub testme()

Dim WSHShell As Object
Dim DesktopPath As String
Dim myCurrentPath As String
Dim myFileName As Variant

Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.SpecialFolders("Desktop")

myCurrentPath = CurDir

ChDrive DesktopPath
ChDir DesktopPath

myFileName = Application.GetOpenFilename("Text Files, *.txt")

If myFileName = False Then
'do nothing
Else
Workbooks.OpenText Filename:=myFileName, ...rest of recorded macro
End If

ChDrive myCurrentPath
ChDir myCurrentPath

End Sub

 

[Jim Hicks]

Rob,

I don't know if this helps but open an explorer window and type "desktop" on
the address bar.
Click "start" "run" and type in desktop.
open a command prompt and type desktop....WTF....gotta look at that
again...my machine is trying to do something with a vbs...shouldn't
be...ignore this line...
open a command prompt and type set this will show the enviornmental
variables. the desktop path for any logged in user can be determined by
examining the user profile
enviornmental variable or the temp or appdata variables. the desktop is
always(usually) located in the same tree structure so if you can determine
the SETting you are half way there.

a newbie example would be to
shell "set > c:\set.txt"
open c:\set.txt for input as #1
while not Eof(1)
'loop through file to look for the text and get path, then modify path to
point to the desktop...

wend
close #1


Another option would be to find out the username logged in and point to the
default path for that user...you would have to verify that the profiles are
stored in a predictable manner (as is usually the case in windows with
normal profiles)

there is code out there that will tell you the logged in user name... I
can't seem to find it right now...
just add the "C:\Documents and Settings\XXXXX\Desktop\


since I can't find the code here is another way... I will look again later
on...
here is the one that finds the computer name...I think it would be similar
but can't remember..

'get computername
Dim strComputerName As String * 255
Dim lngComputerNameLength As Long

lngComputerNameLength = Len(strComputerName)
If GetComputerName(strComputerName, lngComputerNameLength) <> 0 Then
currentuser$ = Left$(strComputerName, lngComputerNameLength)
Else
currentuser$ = Err.LastDllError
End If



run (xp) gpresult...the 5th line or so should show
RSOP results for DOMAIN NAME\USERID on MACHINE NAME: Logging Mode
------------------------------------------------------------
or....
just open a command prompt and type dir
see the default path is to the profile.....


and finally.....opening a file
if you do a run C:\Documents and Settings\USERNAME\Desktop\1.txt
you could run it in a dos prompt also but don't forget the Quotes for long
filenames (spaces won't work in the path otherwise)
this would open the 1.txt file on the desktop in notepad (default
application)
this can be done programattically using the shell, through a batch file or
dos prompt...


I hope this helps a little....BTW I am a newbie programmer (been that way
since the Atari 400)
there are probably much better ways to do this stuff but it is interesting
to see all the ways you can access the profile and files in XP
do a search in the Windows folder for all files and check out some of the
exe's hidden in windows...
also look at MSC like gpedit.msc.
check out the 2 different dos prompts...start-run-command and start-run-cmd
also

Anyhoo, I hope this info helps a little.....but listen to the experts before
me.......