Select range page & save under specific folder

Hii!!

Can someone help me please! Below is my code, I used that to save my excel file in pdf, and then save it in a specific folder.
There are two things:
- When it saves my excel file, its just saving the first page of my worksheet. So I made a calculationsheet, and dependent of the data thats used, I wil have always another calculation. That means, that sometimes it can be only at one page, and another time more pages. Problem is now, that when i save my document, it will give me always 1 pdf File from the first page, and Its like its blocking all the other pages...
- I have saved like you can see my file onder the map Y:\LIVE\digdocs\BA_ANTWERP\.
Now under the file BA-ANTWERP, there is also a subfolder. This subfolder has the same name as my file. Is it possible to save my excelfile under this subfolder? So if my document is called 555, i have to save it automaticaly under map 555 en and it has to be something like Y:\LIVE\digdocs\BA_ANTWERP\555.

I have tried eveerthing, but after 5 hour nothinggg Pleasss someone that can hellp me?



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim FacName As String

FacName = ActiveSheet.Range("R1").Value ' De macro haalt met dit command het factuurnummer op in de factuur, om deze later als naam voor het PDF-bestand te gebruiken.

If Dir("Y:\LIVE\digdocs\BA_ANTWERP\" & FacName & ".pdf") <> "" Then
MsgBox "Het bestand: " & FacName & ".pdf bestaat reeds" '
' De map waarin je de PDF-bestanden in wilt creëeren (bij mij FactuurMail) moet op voorhand aangemaakt zijn!!
Exit Sub 'Verlaat de routine als het PDF-bestand reeds bestaat.
Else
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Y:\LIVE\digdocs\BA_ANTWERP\" & FacName & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, from:=1, to:=2, OpenAfterPublish:=True

End If
End Sub

There are several things that I don't understand.

For the first thing, are you wanting all worksheets in one pdf file? If so, before exportasfixedformat line:

Please Login or Register  to view this content.

If you want a select set of worksheets exported as one pdf file, then:

Please Login or Register  to view this content.

sort of thing is needed. The sheet's index order governs the order of how the worksheets are exported. If you want a specific non-index order, exporting to a scratch workbook is one way.

For thing 2 to get a string for the subfolder name, you can use the FileSystemObject to get the base name of the file or just use:

Please Login or Register  to view this content.

sort of thing. The 5 varies depending on your file extension.

Perhaps a modefication like this would work?

Please Login or Register  to view this content.

Alf

Thankssssssss!!

Thank you !! :p :D I will try it now!

Originally Posted by Kenneth Hobson

There are several things that I don't understand.

For the first thing, are you wanting all worksheets in one pdf file? If so, before exportasfixedformat line:

Please Login or Register  to view this content.

If you want a select set of worksheets exported as one pdf file, then:

Please Login or Register  to view this content.

sort of thing is needed. The sheet's index order governs the order of how the worksheets are exported. If you want a specific non-index order, exporting to a scratch workbook is one way.

For thing 2 to get a string for the subfolder name, you can use the FileSystemObject to get the base name of the file or just use:

Please Login or Register  to view this content.

sort of thing. The 5 varies depending on your file extension.

Thank you! I will try it now! :D

Originally Posted by Alf

Perhaps a modefication like this would work?

Please Login or Register  to view this content.

Alf

ThAnk you!

Originally Posted by Kenneth Hobson

There are several things that I don't understand.

For the first thing, are you wanting all worksheets in one pdf file? If so, before exportasfixedformat line:

Please Login or Register  to view this content.

If you want a select set of worksheets exported as one pdf file, then:

Please Login or Register  to view this content.

sort of thing is needed. The sheet's index order governs the order of how the worksheets are exported. If you want a specific non-index order, exporting to a scratch workbook is one way.

For thing 2 to get a string for the subfolder name, you can use the FileSystemObject to get the base name of the file or just use:

Please Login or Register  to view this content.

sort of thing. The 5 varies depending on your file extension.

Actually, I just want that it saves 1 worksheet. But dependent of the data , this worksheet will get longer and longer. I have used for example the portrait -mode, But if I print it I cant read anything anymore, the letters are too small, it brings everthing in one page. I just want to save separtely al pages of my worksheet in one pdf. I dont want to collapse al thiss page in one view. If I have one page of information in my excel file, it has to save it in one PDF file. If there are three pages, it has to save all this three pages in one PDF... Do you understand what I mean?

Originally Posted by Alf

Perhaps a modefication like this would work?

Please Login or Register  to view this content.

Alf

This seems a good solution, but if I print my document I cant read it anymore, it collapse everything in one page, and the letters are too small. Its only good If I keep it electronic, where I can use Zoom-function..

Actually is this code brilliant if I can depend it on my data.

If I have one page

FitToPagesWide = 1
FitToPagesTall = 1

If there is more as one page
FitToPagesWide = 1
FitToPagesTall = 2 or more


Someone Any idea how I can modify this code ? I'm grateful for any helppp!

Originally Posted by Alf

Normally this should work ok for 2 to 3 pages more than that the letters gets to small as you allready found out. Will have a go see if I can find another solution.


Alf

Yes, but the problem is sometimes I have 8 or more pages. It depends of the data I have. So when I have to collapse 8 pages in one view, its not very clear to read....

I'm now not sure I understand you problem properly.

As you are talking about first page of worksheet as well as a calculation sheet I would say that the "Array" setup proposed by Kenneth Hobson should be the way to go.

The only thing you could try would be to organize you data on both sheets in a simmilar way so you either use "Portrait" or "Landscape" when creating the pdf file and see if this makes result a bit more readable.

But shrinking eight pages to a readable one page pdf file is not possible.

Alf

Originally Posted by Alf

I'm now not sure I understand you problem properly.

As you are talking about first page of worksheet as well as a calculation sheet I would say that the "Array" setup proposed by Kenneth Hobson should be the way to go.

The only thing you could try would be to organize you data on both sheets in a simmilar way so you either use "Portrait" or "Landscape" when creating the pdf file and see if this makes result a bit more readable.

But shrinking eight pages to a readable one page pdf file is not possible.

Alf

My calculationsheet is actually my only worksheet I have. By calculating my data, i can get on this same page, 10 pages, 8pages of calculation or only one page...its al on the same worksheet..

But why can i dont save al my pages from 1 worksheet under 1 pdf? Its really weird that that is not possible to do :/

Set your print range. ActiveSheet.Usedrange can be used in a recorded macro's hard coded range. The macro recorder is your friend. How well that comes out in a PDF file varies based on your structure and content.

If it's all on one worksheet you could print 8 pages or more. Have a look at the upoaded file.

Run macro "PdfPort", this will save an eight page pdf file (Sheet1.pdf) to folder "C:\Temp\" folder using "Portrait" orintation.

Run macro "PdfLand", this will save an eight page pdf file (Sheet2.pdf) to folder "C:\Temp\" folder using "Landscape" orintation.

The macro "PdfComb" will save a 21 page pdf file (Combined.pdf) to folder "C:\Temp\" folder trying to use both "Landscape" and "Portrait" orintation.

I had to select the "Landscape" orientation first in order to get it work properly, still it adds a number of empty pages. Also the effect of this macro lingers in the printer setting. I had to run "PfdPort" twice in order to get the propper portrait format for file Sheet1.pfd after running macro "PdfComb"

Alf

Setting the print ranges would be something like:

Please Login or Register  to view this content.

Since there are 3 areas, then 3 pdf pages in 1 pdf file would be exported.

IF order is important, you may need to create a pdf for each range and then merge the pdf's into one file. There are various ways to do that. The easiest is if you have Adobe Acrobat Pro. The 2nd would be a 3rd party program. The 3rd would be to create a scratch worksheet and export all sheets. Obviously, the last method can be done by a macro. The key for all these is a means to know which area is to be selected.