CODEWITHSUNDEEP
excelvbaloops
JordiDash
JordiDash

Reputation: 11

Saving a workbook with other workbook's name

I have a template file at its own folder where I copy data into from a bunch of periodic files from a different directory. The process I am attempting to automate is to copy data from first periodic file into the template, save the resulting feeded template naming it as the first data source file ("1" in the example) and then go back to the original empty template in order to loop the process with the rest of periodic files.

My clumsy attempt to this point has been the following:

I first copy data from "1" to "template":

Workbooks.Open fileName:= _
    "C:\sample\template\template.xlsx"
Workbooks.Open fileName:= _
    "C:\sample\1.xlsx"

Windows("1.xlsx").Activate
Range("A2").Select
Selection.Copy
Windows("template.xlsx").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Then I want to save "template" with the added data as "1.xlsx" but into the template folder.

Windows("1.xlsx").Activate
[[ActiveWorkbook.Name.Copy]]?????
Windows("template.xlsx").Activate
[[Saveas.Paste & ".xlsx"]]?????

Since I have no basic knowledge in programming but recording and cleaning up a couple of macro lines, I am really stuck at the "copy filename and save as pasting" step. I assume there is a more efficient approach to the whole process but by now I just want to make the second part work if possible.

If you think I really need some basis before attempting stuff like that just give me the keywords to adress my learning.

Thanks in advance ;)

Upvotes: 0

Views: 69

Answers (1)

JordiDash
JordiDash

Reputation: 11

After discovering Name As and FileCopy I got the whole loop working for all files in the same directory:

Dim xFd As FileDialog
Dim xFdItem As Variant
Dim xFileName As String
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
xFileName = Dir(xFdItem & "*.xls*")
Do While xFileName <> ""
With Workbooks.Open(xFdItem & xFileName)

ActiveWorkbook.Sheets(1).Activate
Range("A2").Select

Workbooks.Open fileName:= _
"C:\sample\template\template.xlsx"

Workbooks("template").Sheets(1).Range("A2").Value2 = _
ActiveWorkbook.Sheets(1).Range("A2").Value2

ActiveWorkbook.Save
ActiveWorkbook.Close

FileCopy "C:\sample\template\template.xlsx", "C:\sample\out\template.xlsx"

Name "C:\sample\out\template.xlsx" As "C:\sample\out\" & 
ActiveWorkbook.Name

ActiveWorkbook.Save
ActiveWorkbook.Close

End With
xFileName = Dir
Loop
End If

Upvotes: 1

Related Questions