Opening a workbook and using that name thruout the macro 3

[eja5866]

Hello,

I'm trying to set up the beginning of this macro to Prompt the user to pick a file which they want the macro to parse thru. Only problem is the string variable that I am storing still has the path connected to it (ie: C:\Temp\Chosen_File). Thus when the rest of the macro is trying to call this string variable, it fails.


Here is my code so far:


Dim rwIndex As Integer
Dim LoadSheet As String


'Checking to see if batch sheet is open, if not allow the user to open it

LoadSheet = Application.GetOpenFilename("Workbooks (*.xls),*.xls", 1, "Please find new batch file to Load&quot
Workbooks.Open LoadSheet

IsThisWorkbookOpen (LoadSheet)
Windows(LoadSheet).Activate



Both of the calls for Loadsheet fail since the path is still in the string.

What command do I use to just save the filename ONLY in my string variable?


Thanks for your help - I'm a rookie VBA user.

Ed


Kind Regards,

Edward Apacible
International Flavors & Fragrances
Ph: 732-264-4500 Ext. 3469
Fax: 732-335-2350

 

[BuGlen]

To extract a file name from a full path, use the InStrRev() and Mid() functions to locate the last backslash and extract the file name:

    Dim strPath As String, strFileName As String
    
    strPath = ActiveWorkbook.FullName
    MsgBox strPath
    strFileName = Mid(strPath, InStrRev(strPath, "\") + 1)
    MsgBox strFileName

Hope this helps.

Glen Appleton

VB.Net student.

 

[SkipVought]

Ed,

How bout this...

    Dim wbNew  As Workbook
    LoadSheet = Application.GetOpenFilename("Workbooks (*.xls),*.xls", 1, "Please find new batch file to Load")
    If LoadSheet <> False Then
        Set wbNew = Workbooks.Open(LoadSheet)
    Else
        Exit Sub
    End If
    wbNew.Activate

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

Ed,

What I meant to say is that you do not need the File Name under ordinary circumstances. In fact, you do not need to activate or select workbooks/worksheets unless you want then viewed by the user. When you activate and select, you slow down your program.

See faq707-4105 How Can I Make My Code Run Faster?



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[eja5866]

Thanks guys for both of your help.

I used a little of Both types you guys mentioned as such:

LoadSheet = Application.GetOpenFilename("Workbooks (*.xls),*.xls", 1, "Please find new batch file to Load&quot
If LoadSheet <> &quot;False&quot; Then
Set BatchSheet = Workbooks.Open(LoadSheet)
LoadName = BatchSheet.Name
SheetName = Left(LoadName, InStr(1, LoadName, &quot;.&quot - 1)

Else
Exit Sub
End If

I used the Batchsheet.name since it is able to give just name.xls without the path. And I used the Left/Instr function to trim off the .xls from the name since the sheet names are the same as the workbook name.

Thx again.

Kind Regards,

Edward Apacible
International Flavors & Fragrances
Ph: 732-264-4500 Ext. 3469
Fax: 732-335-2350

 

[SkipVought]

Edward,

Glad that you got the help you were looking for.

It looks to me as if Glen provided the bulk of the advice that you foud so helpful.

It is appropriate to Thank BuGlen for this valuable post! both for the sake of the person supplying such expert advice, but also to identify it to other Tek-Tip members who may be looking for sound advice.

If you choose to recognize Glen Appleton in this way, notice the hyperlink in the lower left-hand corner of his post.



Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[dc20]

Skip,

I am new to VBA and found this line command you used very interesting as it performs two operations at once...

If LoadSheet <> False Then
        Set wbNew = Workbooks.Open(LoadSheet)

But even beyond that it seems a little counter intuitive to me as it looks like it is assigning wbNew to a &quot;method&quot; (ie Open) rather than just the object itself (the filename). I DO understand it's purpose and see it's usage in the latter Activate line (very effective, thus the specific file name doesn't need to be used). Can you or someone provide me just a little insite as to why this works ? All I'm saying is that if I hadn't seen it used this way, I would have never thought &quot;Setting&quot; something to &quot;Open&quot; would be a legal command.

thanks.

 

[Bowers74]

The line of code written that way can be a bit confusing to read. What it is actually &quot;saying&quot; is:

If a file has been selected in the GetOpenFilename dialog, then set wbNew to LoadSheet.

In order for this to work though without getting a &quot;Subscript out of Range error&quot;, the Workbook needs to be opened. Essentially, the line of code is opening the LoadSheet (if one has been selected) and then &quot;setting&quot; it as wbNew.

You have to kind of read it backwards! ;-)



Happy Ho Ho!!! [Cheers]

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884