Save Excel Sheet In PDF Format With The Name Of Workbook
Jun 23, 2012
I have the below macro steps to save an excel sheet in PDF format in "C:UsersxxxxxDesktop" and with the name of the workbook.
But when the PDF report gets published in c: drive , the PDF file name has .xlsm added to it instead of just the file name.
I would like to know how to publish the file in PDF format just in the name of the workbook.
Find the below macro steps:
Dim docname As String
docname = ThisWorkbook.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UsersxxxxxDesktop" & docname, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
The starting sheet has lot of text cells with empty columns and rows between them, without pattern. I need to do the following:
-Copy the original sheet and work on the renamed copy -Remove all cells with bold font text (these are titles) -Create a new sheet -Transform the table to 1 column in the new sheet (no order required) -Remove empty rows -Save as CSV files with 2950 rows maximum each, with same name+number
I'm a fan of the Excel 2010 table styles, but can't figure one thing out. When I create a table in excel (Ctrl+T), I like to reformat it with a new defined style. When I save the style, I can use it while I'm in that instance of excel, but when I close and reopen excel, the style is gone. How to save the custom formats to they are always available?
Automating Excel from Access, I'm attempting to generate a single sheet workbook and save to a Sharepoint site. This process works fine on my machine (XL2007, XP), but on a coworkers computer (XL2003, XP), the code throws a 1004 error on the save as line. However, the really odd thing is that stepping through the code doesn't throw an error on the coworkers machine.
Here's the sub being ran; the line it errors out on is the first branch in the .saveas block.
I have an expense report, which was originally done in Excel 2003. It still prompts users to open as read only however if they select no, they are not prompted for the password, and it has allowed them to save, so when the next person opens it, they have the previous person's report instead of the clean workbook. I have tried everything to put a password onto it. I know this is really basic stuff, but maybe I am missing a step? I want the end user to be able to make all the changes they want in read only mode and then do a save as, but if the select No when prompted "Open as Read-only?" they should have to put a password in.
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code: Sub SaveSheetAsNewBook() Dim wb As Workbook Dim InitFileName As String Dim fileSaveName As String Dim wshape As Shape InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
have a basic knowledge of VBA. The task I am trying to perform with VBA is to
1. Copy from sheet1 A1 to sheet2 E9 2. Copy from sheet1 B1 to sheet2 E11 3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls) 4. Copy from sheet1 A2 to sheet2 E9 5. Copy from sheet1 B2 to sheet2 E11 6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls) 7. Repeat the process down columns A and B to the end of the columns. 8. Columns A and B will end at the same time but the data and the last cell will change with each scan added to the columns.
This code will accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.
VB: Sub MoveData() sName = "temp" Const csPath As String = "C:Documents and SettingsmcgaulcDesktopTestFile" 'MyName = ActiveWorkbook.Name
Here are some images of the data sheet and the saved sheet
Sheet1.jpg Sheet2.jpg Note: sheet2 in image is "temp" sheet in code
I am having trouble getting Excel 2007 on my work computer to save as .xlsx by default.
I have opened the Office Button > Excel Options > Save > Save files in this format and selected "Excel Workbook (*.xlsx)," and if I save as before closing Excel then it works perfect and saves the file as .xlsx. However if I close Excel, re-open it later and save a new file the default "Save as type:" is "Excel 97-2003 Workbook (*.xls)" and if I open the save options again the "Save files in this format" option is reverted back to "Excel 97-2003 Workbook (*.xls)."
I have finally overcome the [Compatibility Mode] issue by saving a file named "Book.xltx" (not "Book1.xltx") in the two default locations "C:Program FilesMicrosoft OfficeOffice12XLSTART" and "D:Documents and SettingsusernameApplication DataMicrosoftExcelXLSTART" (we use the D: drive at work for personal files). I thought this would solve the save as issue but it hasn't. I have also changed the "Save files in this format" before saving the .xltx files in the locations to apply the settings to those specific files but that hasn't worked.
It is on my work computer so I am limited in what settings I can change because they have them pretty well locked down.
I got a workbook with a database in sheet1. I filter that database and copy - paste values the results to a new sheet. Next, I save that new sheet to somewhere on my drive. The thing is that I want to go back to my original database, and continue filtering. But that workbook is no longer the "active workbook". This is how it works:
sub DifferentClients() For Each FieldWorkBy In Array("Client1, Client2,...") MyDatabase.AutoFilter Field:=CountryCode, Criteria1:=Array( _
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB
There is only one task left I need to automate. On closing the document I would like a copy to be saved in a specific folder and be given the file name of a cell (B9). Obviously I would not want the 'saved copy' to retain the Incrementing invoice number or the clear cells function. Even if the invoice copy was exported as a flat jpeg that would be fine. I am using Office Exce 2003 (Sp2) - I have attached my invoice should anyone be kind enough to take a look.
Working with a macro enabled workbook that has many sheets what would be a good code that would save Sheet1 into a new MACRO enabled workbook so that the 'Buttons' (which are linked to macros) on this specific sheet will be the only macros that will be copied to the new workbook as opposed to the rest of the macros that are found in different sheets?
I have many worksheets in a workbook that need to be saved if a user changesanything on them. These sheet names all end in "....SD" and the code needs to only run on those sheets. I have learned alot from the forum but not enough, just yet . . This is what I have so far:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim sht As Object For Each sht In ThisWorkbook.Sheets If LCase(Right(ws.Name, 2)) = "sd" Then MsgBox(Prompt:="You must save changes. Save now?", Buttons:=vbYesNo) = vbYes Then ThisWorkbook.Save End Sub
It doesn't like the 2 "Then's". (Don't laugh - I'm trying.)
I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal.
If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
Windows 8.1 Excel 2010, I open a Template, make some changes in the template then I want to save the modified template as a Template ".xltm" under a new name. I'm using the following code for the new workbook name (which works except for the file extension);
I need VBA code for the following - I have a worksheet with seven colums of data (A to G) - I need to copy the first column (A) from the active worksheet then open master workbook called 'master' and paste the data in to column D - then save the 'master' as the name in cell Z1 of the 'master' workbook. Once this has been been completed I need to repeat the process but this time copying column (B) and so on.