Script to open filename with suffix at the end of it?

**ks100** · 12-12-2013, 01:43 PM

This is the plain text version of what the script needs to do:

1. Capture current workbook name and save as variable

2. Open C:\files\(saved variable)001.xls

3. Make (saved variable)001.xls the active window

Then from here on out I will take over with the rest of the script I already have.

Thanks!

**JOHN H. DAVIS** · 12-12-2013, 01:50 PM

Maybe:

Sub ks100()
Dim x As String
Dim y As String
x = ActiveWorkbook.Path
y = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
Workbooks.Open "\" & x & y & "001.xls"
End Sub

**ks100** · 12-12-2013, 01:56 PM

Originally Posted by JOHN H. DAVIS

Maybe:

Sub ks100()
Dim x As String
Dim y As String
x = ActiveWorkbook.Path
y = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
Workbooks.Open "\" & x & y & "001.xls"
End Sub

Thanks, but unfortunately the active workbooks path is not the same as where its 001 counterpart is stored. The 001 copies are all stored in C:\files, so how would that change the formula?

**JOHN H. DAVIS** · 12-12-2013, 02:15 PM

Sub ks100()
Dim x As String
Dim y As String
x = "C:\files" 'here
y = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
Workbooks.Open "\" & x & y & "001.xls"
End Sub

**alansidman** · 12-12-2013, 01:56 PM

Sub getname()
Dim wbTarget As Workbook
Dim wbThis As Workbook
Dim strName As String
Set wbThis = ActiveWorkbook
strName = ActiveWorkbook.Name
strName = Left(strName, Len(strName) - 4)
MsgBox (strName)
Set wbTarget = Workbooks.Add
    With wbTarget
        .SaveAs Filename:="C:\files\" & strName & "001.xls"
    End With
wbTarget.Activate
End Sub

Script to open filename with suffix at the end of it?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Script to open filename with suffix at the end of it?

Re: Script to open filename with suffix at the end of it?

Re: Script to open filename with suffix at the end of it?

Re: Script to open filename with suffix at the end of it?

Re: Script to open filename with suffix at the end of it?

Thread Information

Users Browsing this Thread

Similar Threads

Trying to open a website using Workbooks.Open Filename:= fails- gives back page source

VBA script- save file with current filename

How to close open workbooks from an application get open filename call ?

set filename to <filename-date> on open

script to change filename

Bookmarks

Bookmarks

Posting Permissions