Automation Code Problem from Access to Excel

[Tony]

I have a problem changing the drive in my Excel application that I
create with code in Access. The Drive is changed in Access, not in
Excel. The code should check to see if "A" drive is ready, and if so,
start the File open dialog in drive "A". It works, but for the Access
application it is run from, not the new Excel Workbook. My pertinent
code is below. Any help will be appreciated.


Private Sub cmdRunBHmacro_Click()
Dim xlObject As Excel.Application


Set xlObject = New Excel.Application
xlObject.Visible = True

Dim fs
Set fs = CreateObject("Scripting.FilesystemObject")
If fs.Drives("A").IsReady = True Then
ChDrive "A"
End If

FileToOpen = xlObject _
.GetOpenFilename("All Files (*.*), *.*", , "Open BH Loop Data File")
If FileToOpen <> False Then
myFileName = fs.GetFileName(FileToOpen)
End If

If FileToOpen = False Then
End
End If


xlObject.ScreenUpdating = True
xlObject.Workbooks.OpenText Filename:=FileToOpen, _
DataType:=xlDelimited, Tab:=True
Windows(1).WindowState = xlMaximized

End Sub

 

[Tom Ogilvy]

You have the fully qualified path in FileToOpen

then you strip off the path using GetfileName

Just us FileToOpen and it should save it to the location selected by the
user.

 

[Tony]

Tom,

Thanks for your reply. I want the GetOpenFilename dialog box to start in
drive "A" if a floppy is inserted. The problem I am having, is that the
code, as written, changes the drive for the Access application it is
written in, not the Excel application created, which is the one the file
is being opened for.

Tony