ExcelBanter - File names

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - File names (https://www.excelbanter.com/excel-programming/313166-file-names.html)

Hi gang
I am trying to copy data from one sheet to another. I am copying from
my_file20041001 to my_other_file20041001. I can use the following code to
accomplish this,specific to each sheet, but I would like generic code that
would extract the last 8 characters of the file name to use for opening the
other file.

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file20041007.xls"
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

How do I do it?

Thanks

Hi BOB

=RIGHT(activeworkbook.filename,12)

(i'm using 12 as you'll need the *.xls at the end)

so in your code (untested)

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file" & RIGHT(activeworkbook.filename,12)
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

Cheers
JulieD

"BOB" wrote in message
...
Hi gang
I am trying to copy data from one sheet to another. I am copying from
my_file20041001 to my_other_file20041001. I can use the following code to
accomplish this,specific to each sheet, but I would like generic code
that
would extract the last 8 characters of the file name to use for opening
the
other file.

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file20041007.xls"
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

How do I do it?

Thanks

THanks for the reply

My code Copy/pasted

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\dfs01\shares\groupdirs\0535\Reports\"
Workbooks.Open Filename:= _
"\\dfs01\shares\groupdirs\0535\Reports\dailylo g" &
Right(ActiveWorkbook.Filename, 12)
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

Ths gives me...

object doesn't support property or method

Thanks

"JulieD" wrote:

Hi BOB

=RIGHT(activeworkbook.filename,12)

(i'm using 12 as you'll need the *.xls at the end)

so in your code (untested)

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file" & RIGHT(activeworkbook.filename,12)
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

Cheers
JulieD

"BOB" wrote in message
...
Hi gang
I am trying to copy data from one sheet to another. I am copying from
my_file20041001 to my_other_file20041001. I can use the following code to
accomplish this,specific to each sheet, but I would like generic code
that
would extract the last 8 characters of the file name to use for opening
the
other file.

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file20041007.xls"
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

How do I do it?

Thanks

Try:

ActiveWorkbook.Name

A followup:

Did ChDir work ok for you using that UNC name?

You didn't actually depend on that line (since you included the path in the next
line).

But here's a post from Rob Bovey/Tom Ogilvy that works with UNC paths.

========

Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long

Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub

Example of usage

Sub GetFile()
On Error GoTo ErrHandler
ChDirNet "\\LOGD0FILES\OGILVTW\Docs\Temp"
Exit sub
ErrHandler:
MsgBox "Couldn't set path"
End Sub

Use like ChDir and ChDrive combined.

BOB wrote:

THanks for the reply

My code Copy/pasted

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\dfs01\shares\groupdirs\0535\Reports\"
Workbooks.Open Filename:= _
"\\dfs01\shares\groupdirs\0535\Reports\dailylo g" &
Right(ActiveWorkbook.Filename, 12)
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

Ths gives me...

object doesn't support property or method

Thanks

"JulieD" wrote:

Hi BOB

=RIGHT(activeworkbook.filename,12)

(i'm using 12 as you'll need the *.xls at the end)

so in your code (untested)

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file" & RIGHT(activeworkbook.filename,12)
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

Cheers
JulieD

"BOB" wrote in message
...
Hi gang
I am trying to copy data from one sheet to another. I am copying from
my_file20041001 to my_other_file20041001. I can use the following code to
accomplish this,specific to each sheet, but I would like generic code
that
would extract the last 8 characters of the file name to use for opening
the
other file.

Sheets("Summary").Select
Range("E1:E29").Select
Selection.Copy
ChDir "\\my_path"
Workbooks.Open Filename:= _
"\\my_path\my_other_file20041007.xls"
Sheets("Summary").Visible = True
Sheets("Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.Visible = False
End Sub

How do I do it?

Thanks

--

Dave Peterson