Saveas with name of another file

[sowetoddid]

This is the code I am using to open another excel file, copy data an
paste it into the original file. Is it possible to add a line in thi
macro that will do a saveas on the original workbook (ThisWB)? I woul
like to do a saveas with the name of the other file (OpenedWB) and "-F
for Finished.

So, ThisWB would have a saveas with the name being "OpenedWB - F".

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpecial _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub


Thank you

 

[sowetoddid]

I am not sure if I described this sufficiently...

Using sample names,

Workbook1 = Music
Workbook2 = Animal


Using a macro in "Music", I would like to access the file name in
"Animal" and do a saveas on the Music File. The result is three
files...

"Music"
"Animal"
"Animal - F"

Hope that helps

 

[sowetoddid]

I am finding this somewhat difficult to explain. Ok, one more time.

Starting with the "Music" file I run the macro

The macro accesses "Animal" file, copies some of the data and pastes i
to the "Music" File


Next, I would like the updated "Music" File to be saved as "Animal - F
(or what ever the name of the accessed file, adding "- F" to the end)


Any ideas

 

[sowetoddid]

Is this right??

ActiveWorkbook.SaveAs FileName:="OpenedWB - F"


Also, how would I designate a specific directory for the save?



Thanks

 

[Frank Kabel]

Hi
use ChDir
prior to saving the file

 

[sowetoddid]

A little uniformed on this,....can you be more specific on how to d
this.

I am a Senior Forum member only because I ask sooo many questions!!
just became interested in the VB code and found a lot of applications.
The problem is I only have enough programming skill to be dangerous.


Thanks

 

[Frank Kabel]

Hi
e.g.
sub foo()
chdrive "C:\"
chdir "C:\temp"
ActiveWorkbook.SaveAs FileName:="OpenedWB - F"
end sub

 

[sowetoddid]

Thansk, Frank!

That got me all the way there, but with the wrong file name. I don'
think it was explained right...

OpenedWB is a variable name for whatever the file I am opening is.
don't want to do as saveas with literally "OpenedWB". It should be
for example, "5-18-04 - F". Where OpenedWB, in this case, i
5-18-04.xl

 

[sowetoddid]

When I put this coede at the end of the sequence, is OpenWB no longer a
active variable

 

[Frank Kabel]

Hi
now you have lost me

 

[sowetoddid]

This is the code that I am using...

Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpecial _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub


When I run the macro, the open file window appears and I choose a fil
(Variable name = OpenedWB), the data is copied from the "Opene
Workbook" and pasted into the the original workbook (Variable name
ThisWB).

I want to add a code at the end that resaves "ThisWB", naming i
Animal, Music, or whatever the opened workbook was called. I woul
like to get the filename from the opened workbook and use it as th
filename for the original workbook.


Does that help at all

 

[Frank Kabel]

Hi
try
Sub testIt()
Dim fname
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpecial _
xlPasteValuesAndNumberFormats

fname = OpenedWb.name
OpenedWB.Close False
End If

ThisWb.saveas fname


End Sub

 

[sowetoddid]

That's what I was looking for.


THANK YOU, Frank

 

[sowetoddid]

Trying to modify the saved name just slightly...

How do I remove the .xls??


ThisWB.SaveAs fname & " - F

 

[sowetoddid]

Figured it out...


fname = Left(OpenedWB.Name, Len(OpenedWB.Name) - 4)


Thanks

 

[Frank Kabel]

Hi
another option: use replace for this