can worksheet names be seen without opening the workbook? 1

[marduk813]

I have a small userform that prompts the user to select an existing workbook to be used as output for a macro. I also need the user to specify which sheet in the output file will be used. Is there a way to display a list of the worksheets in a workbook without actually opening the workbook?

My macro is set up as such that the output file does not have to be selected each time. It stores the file and path names in cells on a worksheet. This info will be used every time, unless the user wants to select a new output file. Previously, I've just had the user directly modify the path name, file name, and sheet name directly in the respective cells, but I would like to make it more user-friendly.

 

[SkipVought]

Hi,

Not that I know of. But what's the problem with opening the workbook in order to get the sheet names?

set wb = workbooks.open sWkBkName
with wb
  .visible = false
  i=0
  for each ws in .worksheets
    sNames(i) = ws.Name
  next
  .close
end with
set wb = nothing

Skip,
[sub]
[red][/red]
[/sub]

 

[marduk813]

Thanks Skip. I thought it might be faster to not open the workbook, but this will work. I disabled screen updating, so it all looks pretty seamless to the user. Thanks!