Workbook Title | Problem with Activating Sheet

[ronnockoch]

Hello again,

Back again with another problem.

In brief: What I'm trying to do is open a workbook in a set directory (user inputted, but that's not the problem right now). The workbook location is inputted via a filedialogue box.

I need to open the workbook, activate it, do stuff to it (Copy data out of it specifically) then close it.

I'm running into problems after I open it and try to activate it.

While I have the workbook location, I don't have the name figured out on my end in the code because it always changes.

What would be the best way to get the workbook name? Would it be through string manipulation? (instr) or is there a simpler way.


Here's what I thought to start, but it doesn't seem to work like as i would like.

source = "...\SourceDirectory\File.xlsx"
Workbooks.Open filename:=Source 

Workbooks(Source).Activate '' This is where I know the code errors, because it's trying to activate a workbook with name "...\SourceDirectory\File.xlxs"

 
Workbooks(Source).Close '' I'm assuming this also would have to be the filename and not the path

Let me know if you need me to clarify anything

 

[timfritsch]

Have you considered displaying the File Open dialog box and selecting the file? Something like

dim ans as string, fileName as string

source = "...\SourceDirectory\"
  
ans = Application.Dialogs(xlDialogOpen).Show(source)

fileName = activeworkbook.name

Tim

 

[ronnockoch]

Have you considered displaying the File Open dialog box and selecting the file? Something like

dim ans as string, fileName as string

source = "...\SourceDirectory\"
  
ans = Application.Dialogs(xlDialogOpen).Show(source)

fileName = activeworkbook.name

Tim

Tim,

edit:

Sorry I spoke to quickly.
That doesn't work from my end for a couple reasons

1) I'm not opening the file with the dialogue box in this step, the step where the file is selected (with the file dialogue) is before this.

I have a userform that asks the user to input a destination and a source file. Those two locations are then passed to a textbox, where that info is stored. The file hasn't been opened yet.

Once both Source and Destination have values in them, the user is able to click on a third button which will be needing to open the file(s).

That's why the activeworkbook doesn't work for me, because up until the workbook is opened in the third step, it hasn't been opened yet. It's being opened using a direct location, defined by the "source" variable

 

[timfritsch]

If textbox1 is the path and textbox2 is the file name: source = textbox1 & "\" & textbox2. Would this work?


Tim

 

[ronnockoch]

If textbox1 is the path and textbox2 is the file name: source = textbox1 & "\" & textbox2. Would this work?


Tim

The source file is the direct link to the file "C:\Users\...\File.xlxs"
The destination file box is "C:\Users\...\"

Thinking about it, I could probably just get the filename from the destination filebox, as I have to strip off the filename anyways to get it to be the parent folder.

hm..