Select Sheets "Sheet3". Select ThisWorkbook. Gary's Student Gary's Student Using Selection only seems to export a blank sheet in Excel Sheets Array 1,2. It wasn't until I used: ThisWorkbook. I am now getting an error with this Array 1,2 what is this meant to be?
Sorry this is after the fact. I haven't logged on in awhile. Filename should include the file path to the location you want to save to as well as the file name.
Name Debug. Print Sheets i. Name Next i Sheets shtAry. Select Debug. Print ThisWorkbook. Mine only works with the first two sheets because that's what I was going for in my code. My answer wasn't really an answer, more of a clarification about using ActiveSheet instead of Selection because I didn't have the reputation to comment at the time. Your solution is much better for using multiple sheets. The Overflow Blog. At first I couldn't get your original code to function as designed.
Don't know if it was a glitch in my computer happens or if it was something in the code. I found the original website that holds the code www. Haven't studied it yet to compare with your posting. Here is the edit to save with the workbook name : Code:. I tried that wsA to wsB switch and I never could get it to run through without an error. I was on the right track It works perfect. Thank you so much! One other question I'm not sure what is causing it to be there except that it's the entire file name.
Try this : Code:. Amazing work. Thanks so much! You are welcome. L2rc1N1 New Member. Joined Mar 28, Messages 1. You must log in or register to reply here. Similar threads B. Question Convert macro enable file to excel file with specific naming convention. Bathur01 Jan 4, Excel Questions. Replies 2 Views Jan 4, Bathur Solved VBA needed for save a range as pdf.
Let's take a look at it:. By my count, the PageSetup object has the 48 properties that I introduce in the table below. The purpose of this table is simply to introduce these properties and their main purpose. However, I explain some of the properties you may want to explore when working with the ExportAsFixedFormat method below. This third example builds on those 2 examples in particular example 2 above to show how you can improve the results you obtain when carrying out an Excel to PDF conversion using VBA.
Therefore, in this particular section, I only explain line-by-line the first section of the macro. In other words, the With… End With block. The With statement allows you to execute a series of statements on a particular object ActiveSheet.
PageSetup in this case without having to repeat the object qualification every single time. In other words, all of the statements within the With… End With block rows 2 to 8 below make reference to the object that appears on the opening statement ActiveSheet.
The object that appears on the opening statement is the page setup description of the active sheet. The object is obtained by using the Worksheet. PageSetup property. In this particular situation, the Worksheet object is the active sheet ActiveSheet. The PageSetup. CenterHeader property allows you to set what appears in the center section of the page header. You specify the text that you want to appear in this section as a string. You can generally apply the syntax and explanation I provide here for the CenterHeader property to the following roughly equivalent properties:.
You can use the PageSetup. Orientation property for purposes of setting the xlPageOrientation value that specifies the page orientation. The Orientation property can take the following 2 values :. You generally specify the relevant range using A1-style references and as a string. Under the A1-style cell referencing style, you use letters for column headings and numbers for rows. This range is from cells B5 to F PrintTitleRows property allows you to specify that a particular row or set of rows should be repeated at the top of each page.
Generally, you specify the rows to be repeated as a string using A1-style references. In this particular example, I don't explicitly type A1-style references in the code. Rows 5. You can generally use the same principles and syntax that I explain here in connection with the roughly equivalent PageSetup.
PrintTitleColumns property. By using the PageSetup. Zoom property, you can determine how Excel scales the relevant Excel worksheet for printing or, in this case, conversion to PDF. As a general rule, you specify the zoom as a Variant value between 10 and percent. Whenever PageSetup. Zoom is set to False, the properties that determine how Excel scales the Excel worksheet are PageSetup.
At the same time, if the PageSetup. FitToPagesTall property for purposes of setting the number of pages tall to which the relevant Excel worksheet is scaled to when printing or, in the case of the example in this tutorial, converted to PDF. This is done in…. The syntax and purpose of the PageSetup. In other words, you use the FitToPagesWide property to specify the number of pages wide the relevant Excel worksheet is scaled to when printing or converting to PDF as in this example. As anticipated above, this End With statement simply ends the With… End With block that we've just analyzed.
As I show in this example, you can make several changes or adjustments that influence the resulting PDF file by using the PageSetup object. You can use this parameter to set the filename of the resulting PDF file. Visual Basic for Applications allows you to do either of the following :. The use of the Filename that I make in these first 3 examples may work for you in certain circumstances. After all, the material structure of those macros is enough to help you save Excel worksheets, workbooks, ranges or charts as PDF files.
However, in several situations, you'll want to use the Filename parameter in a slightly different way for purposes of creating more sophisticated macros that save Excel files as PDF.
Let's take a look at some different ways you can use the Filename parameter:. If you're creating a macro to save Excel files as PDF, and those PDF files must always be saved in the same folder, you can simply hard-code the relevant file path and name using the Filename parameter of ExportAsFixedFormat. Notice, however, how I've specified the full path of the file using the Filename argument. The consequence of this change is that the resulting PDF file is no longer saved in the default folder.
Instead, the PDF is saved in the folder that you specify with the Filename argument. In the example above, the file is saved to a OneDrive folder called Power Spreadsheets. You don't necessarily need to hard-code all of the details in the Filename parameter. You can, for example, concatenate different items for purposes of building a more flexible filename structure. In other words, if you use this Filename structure, the new PDF file is saved in the same folder as the source Excel workbook.
The reason for this is that the Workbook. Path property returns a string with the complete path to the relevant workbook. In the case of the example above, the relevant workbook is the active Excel workbook returned by the Application.
ActiveWorkbook property. Name property using the Application. ActiveSheet property to return the name of the active worksheet as the actual filename within the Filename parameter. The file path continues to be provided by the Workbook.
Path property. In this particular case, the macro uses i the Application. ActiveSheet and Worksheet. Range properties to return cell F5, and ii the Range. Value property to return the value in that cell. You can achieve similar results using slightly different structures. This includes, for example, naming the converted PDF file by concatenating several strings of text.
In some cases such as when concatenating several strings of text to specify the PDF filename , it may be more appropriate to declare a variable at the beginning of the procedure to store the applicable string. Whenever you use a worksheet cell as the source for the PDF filename as in the example above , make sure that the cell contains no forbidden characters.
The VBA code samples that I provide in this section are just some of the examples of the different ways you can work with and generate the Filename argument of the ExportAsFixedFormat method. As you can see, Visual Basic for Applications provides significant flexibility in this regard. Even though the topic isn't precisely the same as that of this blog post, some of the principles and examples I provide there may be helpful to you when working with the Filename parameter of the ExportAsFixedFormat VBA method.
You can use the Application. This allows the user of your VBA application to specify the full path and filename. I cover the Application. For purposes of this macro example, it's enough to know that the GetSaveAsFilename method:. The 2 blocks of code I highlight in the image above are substantially the same as those that appear in other examples of VBA code within this Excel tutorial. The only difference is in the Filename parameter , which is the topic we're focusing on in this section.
I cover the topic of VBA variables in more detail here and here.
0コメント