need help with using VBA to access a second spreadsheet

[adam_kroger]

I am writing a "client" spreadsheet using userforms. It needs to be
able to read from a "data" Workbook, and later on it will need to
write back to that workbook. I do not want to "lock" up the "data"
workbook for the entire duration. When finished, the "client" will
run with the Excel application hidden, so only the userforms will be
visible.



I am using "Application.GetOpenFilename" to find the name of the file
and storing that string in a cell.

I do not want the user to see the "data" workbook.



Problems I am experiencing:

-Opening the data workbook with the Excel application being "hidden"
the entire time

- Application.GetOpenFilename returns the FullName (as string) but
some functions want only the Name



Separate issue:

When executing the close command from the userforms, the VBA is
leaving the Excel application still running. How can I make the
actual Excel application close, unless of course the user has other
spreadsheets open?

THANKS

 

[papou]

Hello

- Application.GetOpenFilename returns the FullName (as string) but
some functions want only the Name

Dim Fullname, JustBookName As String
Fullname = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls")
If Fullname <> False Then
JustBookName =
CreateObject("Scripting.FilesystemObject").GetFileName(Fullname)
MsgBox JustBookName: End If

HTH
Cordially
Pascal

 

[Dave Peterson]

So you're hiding the application with something like:

application.visible = false
'do something to load the userform
application.visible = true


'and
'maybe in the userform_initialize procedure???

Dim myFileName As Variant
Dim JustName As String
Dim wkbk As Workbook

myFileName = Application.GetOpenFilename
If myFileName = False Then
'user hit cancel, what happens?
Else
'keep going
JustName = Mid(myFileName, InStrRev(myFileName, "\") + 1)
Set wkbk = Workbooks.Open(Filename:=myFileName)
'do more stuff
End If

instrrev was added in xl2k. If you are using xl97, you can loop backwards
through the string.

======
While you're testing, make sure you show excel and see if there's any prompts
waiting for you to answer.