Save excel file from another excel file

[Gulvafslibning]

Hello there,

I hope you can help me with this, im pretty much a newb at this.

I got 2 open Excel files, and I would like to press a button in 'Excel file 1' and then it saves the 'Excel file 2' with a name from a cell in 'Excel file 1' automatically (when pressing the button).

Right now I use this:

Sub SaveAsNameInCells()
ActiveWorkbook.SaveAs "C:\Users\Alexander\Desktop\Jensen\Fakturaer\" & Range("C3").Value, 52
End Sub

But then it only saves the Excel file I have the macro in of course.
I would like it to save another open excel file called: "C:\Users\Alexander\Desktop\Jensen\Fakturaer\000-000Fakturaskabelon.xlsx"
I dont want to merge these 2 files into 1. That's why I'd like to make a button for it.

Is this possible?

I hope there are some bright guys out there!

Thanks in advance!


Regards,

Alexander Ross

 

[AlphaFrog]

Try this...

[COLOR=darkblue]Sub[/COLOR] SaveAsNameInCells()
[B]Workbooks("000-000Fakturaskabelon.xlsx")[/B].SaveAs "C:\Users\Alexander\Desktop\Jensen\Fakturaer\" & Range("C3").Value, 52
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Gulvafslibning]

Try this...

[COLOR=darkblue]Sub[/COLOR] SaveAsNameInCells()
[B]Workbooks("000-000Fakturaskabelon.xlsx")[/B].SaveAs "C:\Users\Alexander\Desktop\Jensen\Fakturaer\" & Range("C3").Value, 52
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

YOU sir, are the best! It works flaweless! Though the sheet has to be open to be "in range" but it always is, so it's fantastic!

Great job good sir!

Can I give you some points or something for your help? Or how do you do it here?

 

[Gulvafslibning]

Oh actually have a quick question more which lies closely to this.

How to save/export the file as .pdf instead of xlsx?

I tried with ExportAsFixedFormat, but didn't get it to work with the other excel sheet. Is it possible?

 

[AlphaFrog]

Oh actually have a quick question more which lies closely to this.

How to save/export the file as .pdf instead of xlsx?

I tried with ExportAsFixedFormat, but didn't get it to work with the other excel sheet. Is it possible?

ExportAsFixedFormat is the way to do it.

For example code, do a web search for something like; Excel save as PDF site:mrexcel.com

Then if you still can't get it to work, show what you tried and explain the problem, error, whatever happened.

 

[Gulvafslibning]

Yea I looked into it and found some code that worked, thanks!

But the problem is now that I can only press the button once, then it says "Subscript out of range". I can press both buttons and it saves an excel copy and an pdf copy. If I press the button again it says "Subscript out of range". If I restart the programs I can press the button again. Is there a way to not have to open and close the programs to get new files?

 

[AlphaFrog]

But the problem is now that I can only press the button once, then it says "Subscript out of range". I can press both buttons and it saves an excel copy and an pdf copy. If I press the button again it says "Subscript out of range". If I restart the programs I can press the button again. Is there a way to not have to open and close the programs to get new files?

I don't follow what that means. Referring to "Buttons" doesn't really paint a good picture.

1. Show your procedures (code) for both buttons.
2. Describe what the two procedures currently do. If the code errors, press the Debug button on the error message and report the error and what line of code is highlighted.
3. Describe what you want the two procedures to do.

If you only wanted to duplicate the file (not convert it to PDF) that could be done without opening the file. If you want to convert it to PDF, the file would have to be opened.

 

[Gulvafslibning]

Hi again,

Ah I didn't tell I used the code on 2 clickable forms (buttons). So when I press the first it saves the file as xlsm and the other as pdf.

It all works nicely once, but I can't get it to work more than once then it pops up with the error below. The code I used is this:

Sub SaveAsNameInCellsasXlsx()
Workbooks("000-000FAKTURASKABELON.xlsx").SaveAs "C:\Users\Alexander\Desktop\Jensen AS\Fakturaer\" & Range("C3").Value, 52
End Sub




Sub SaveCurrentToPDF()


    Workbooks("000-000FAKTURASKABELON.xlsx").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Alexander\Desktop\Jensen AS\Fakturaer\" & Sheets("Ark1").Range("C3").Value & ".pdf"


'Reset location




End Sub

I can save it once (to excel or pdf) and then if I try again it says "Subscript out of range" or "Run-time error '9': Subscript out of range"

Both files are open all the time. Im banging my head against the wall on this one.

 

[AlphaFrog]

Try using this...

[COLOR=darkblue]Sub[/COLOR] SaveAsNameInCellsasXlsx()
Workbooks("000-000FAKTURASKABELON.xlsx").[B]SaveCopyAs[/B] "C:\Users\Alexander\Desktop\Jensen AS\Fakturaer\" & Range("C3").Value
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Note: remove the file type argument from the end of the line.

This saves a copy of 000-000FAKTURASKABELON.xlsx and the original workbook name doesn't change. So the 2nd time you run the macro, the code still references the original.

 

[Gulvafslibning]

Thanks!

That was exactly what I needed! Now I can save more than once! HURAAAY

I ran into the problem that both could save only once and that was it. I was so stupid I didn't realize the code of course changed the name of the file when saving it and thus couldn't save it again cause it obviously changed the name of the file and now was "Script Out of range".
So by resetting the location made it work flawlessly now! I can now ultra click spam the button and it now WILL save it more than once!
THAAANKS!