Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File names
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File names
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File names
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File names
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File names | Excel Worksheet Functions | |||
Open Excel file get error with file names that have spaces in the | Setting up and Configuration of Excel | |||
file names | Excel Worksheet Functions | |||
EXCEL97 FILE NAMES TRUNCATED TO THE FIRST LETTER IN THE FILE NAME | New Users to Excel | |||
File Names | Excel Programming |