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 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:
Sub Docsave()
Dim docname As String
docname = ThisWorkbook.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:UsersxxxxxDesktop" & docname, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub
I am looking for a vba to use a button to save a file using the cell as its name and save the file in xlsm format. I am using Excel 2010.
View 12 Replies View RelatedIs there a way to convert all files in folder, in this case, xml in excel format to xls without open them?
I recorded the macro below, but this needs to "Open" and "SaveAs" the current file in folder and there are a few thousands of xml files in folder.
I was wondering if through some ADO or VBA code this can be done without open the files in order to save resources and get the work done faster?
Code:
Sub Convert_XML_to_XLS()
Workbooks.Open Filename:="C:MyPathInputFile.xml"
ActiveWorkbook.SaveAs Filename:="C:MyPathInputFile.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False '
End Sub
what format is used when you save an excel file as "unicode." I am using excel 2002 on XP.
I have been asked to provide a UTF-8 formatted unicode file for use by another program but I am not sure which formatting excel uses.
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 am looking for VBA code that will save Excel 2010 files in semi-colon delimited format without having to chage the universal language options.
View 1 Replies View RelatedI'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?
View 3 Replies View RelatedI have two Sheets, first is Service Data, the second is Log Sheet. In the Service Data sheet I have a column of serial numbers in column A. I also have a column of serial numbers in the Log Sheet, column A.
I'm looking for a conditional format whereas I enter numbers into column A on the Log sheet it will auto highlight the cell if I duplicate a serial number from row A in the Service Data sheet. I would like it to highlight the Duplicated number on the Log sheet. I know new versions of excel make it easier to do this but unfortunately I have Excel 2003.
I've been working on this problem on and off for a number of months now and have just about got it sussed so thought I'd share it with you as it has mostly been down to postings on this board that I've got it in the end.
Thanks especially to 'biggoan' for his post: http://www.mrexcel.com/board2/viewto...136&highlight=
Anyway, this seems to avoid the need for a class module but does need you to install the Acrobat Distiller object references in Tools...References in the VBA editor.
You also need to go into the printer properties of the your Adobe PDF 'Printer' and under Printing Preferences...Adobe PDF Settings deselect the Do not send fonts to "Adobe PDF" option. Why, who knows!
Private Sub Create_PDF()
'Created by Dom Hill with considerable asistance from Biggoan and Mr Excel
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
Sheets("Sales Data").Activate
tempPDFRawFileName = "C:" & Range("A1").Value
'Define the postscript and .pdf file names.
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
' Print the Excel range to the postscript file
ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
'Create PDF File
Dim myPDFDist As New PdfDistiller
myPDFDist.FileToPDF tempPSFileName, tempPDFFileName, tempShowWindow
'Delete PS File
Kill tempPSFileName
Kill tempLogFileName
End Sub
Not sure why the macro creates a log file but if you know more about VBA then you probably would.
I created a financial model in sheet with a macro. The model works as designed. And the workbook can be saved with smaller steps. But with big steps that contains about 250,000 formulas, it seemed to take forever to have the work book saved, I have to canceled it after about 45 minutes. I tried it on different machines and all have the same problems.
View 9 Replies View RelatedI am an excel user and know only some basic functions of excel. My query is how do i save data inputted in an excel sheet to another sheet in database form (tabulated). I will try to explain what my present input and outputs are and what I would like to achieve. I think this would make my requirement more understandable.
Present Input:
My Excel Sheet (has only one record per sheet). This after being filled up, is printed. This sheet gets overwritten when a new record is filled in as it replaces the previous data.
Present Output:
Hard Copy of the form, which is printed.
Required Input:
Excel Sheet (has only one record per sheet). This after being filled up, is printed. This sheet gets overwritten when a new record is filled in as it replaces the previous data.
Required Processing:
As the sheet is printed, it should save the data in tabulated form in the same file on a different sheet.
Required Output:
1. Hard copy of the form, which is printed.
2. Saving the data to another sheet in database format i.e. in tabulated form so that analysis or comparisons may be done.
I have a Userform.Which is working perfectly to send data to excel sheet.
In the userform there is command button (commandbutton1) which is used to send data to excel sheet...This only send data but it actually does not save it into excel sheet.When I close excel sheet it asks do you want to save changes?
But I need when commandbutton1 is press to send data to excel sheet , it should actually save data into excel sheet.
[Code] .....
I have a excel sheet which is completely formula driven and no macros in that.
I want to macro which can save that excel sheet to a specific location.
I need excel 2010 to automatically save at 09:30 and 19:45 every day monday-friday. Also need the file name saved to reflect the time and date it was saved - if possible.
My PC locks after 10 minutes of inactivity - unfortunately this has to happen because of where my PC is situated.
when i open csv file i run this macro and i want to save the file as .xls file
my macro is : ...
providing a macro to save an excel sheet to comma delimited txt file. Also, My sheet has 1st row as table columns and i dont want to export them in my txt file.
View 14 Replies View RelatedI have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
when I open my sheet on another computer, all cell value which contain formula show no value. just blank. formula is link to same workbook on different tab. when I save as again on my desktop, all value appears. excel 2007 is using.
View 3 Replies View RelatedAutomating 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.
Code:
Private Const csSharePointSaveAs = "\sharepoint-us.mycompany.comsitesfinance adminlah blahlah-blahCustomer Publication Tracking.xls"
Private Sub PublishXLtoMOSS()
Dim objXL As Excel.Application 'Object
Dim wb As Excel.workbook 'Object
Dim ws As Excel.Worksheet 'Object
Dim rs As DAO.Recordset
Dim i As Long
'Set objXL = CreateObject("Excel.Application")
[Code] ...........
I need to format the text in a excel.
I have attached a excel in this post. in that excel sheet, i need to take the input from the first sheet. The input contains special characters like : , ; , " .
I want to do the following operations.
1. replace all the special characters with line break
2. some email addresses prefix with To: . I need to remove "To:", "cc:", "1st support contact:"
3. after doing these operations , need to trim the column to remove unnecessary blank spaces.
4. remove the duplicates in the column
5. Sort them.
6. Need to remove some email addresses from the list. eg: "ocgsoy, Ggo", "IRFO"
7.Sort them
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
I can run this code successfully:
Code:
With ActiveSheet.ChartObjects("Chart 2").Chart
.Axes(xlCategory).TickLabels.Font.Size = 20
End With
But this code throws a "This Object Is No Longer Valid" error when it gets to the first .Axes line:
Code:
Sub ChartFormat()
'
' Format Charts macro
'
Dim ch As ChartObject
For Each ch In ActiveWorkbook.Sheets("Summary").ChartObjects
With ch.Chart
.Axes(xlCategory).TickLabels.Font.Size = 16
[Code] .......
I'm using Excel 2007.
How to covert a Excel worksheet to HTML format as outlook's stationary and then call out Outlook to send it as a normal HTML mail, not a attachment mail?
View 4 Replies View Relatedam using Excel 2010 and having issues trying to save a worksheet to a specified file location with the save date....
I have tried several posts form this forum and elsewhere and can't seem to get the macro to do what I want.....
I want to save a 'worksheet' from an open workbook that I use for updating information to the same file path as the workbook with the date the file saved...
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.
View 4 Replies View RelatedWhat I am trying to do is that I have an excel file with macros and it is a read-only file. In order for the user to save, I want them to only be able to save as a .xlsx file as it disables all macros. If for whatever reason, the user wants to save the file as another .xlsm file, they should be allowed but before they save, a "are you sure you want to save as .xlsm?" message should pop up.
All the options in the save as box should still be available in case they want to save in that particular format. Just that the .xlsx should be the default.
I am trying to save a file into a CSV format. When I choose that save as option the file is saved with the commas but a need each field to be surrounded by quotes.
Is there an easy way to do this? I know I could append the " around the data in each cell with a formula, but I'm hoping there is a cleaner way to do it.
I would also like to set the extention of the saved file to .txt when I save it, but Excel 2000 does not seem to let me do that.
I have 5 worksheets in a workbook.
I would like to save the Sheet5 in pdf form in a particular directory d: est , instead of taking print of the same.
The data range in Sheet5 is A1:Z. The PDF should be A3 size and landscape orientation.
I'm trying to format my email generated of excel sheet to have an HTML formal before they are sent out using MS Outlook.
I have googled, searched your forums and also tried to figure out a solution with the existing helps on the internet. I need help on how to format emails genarated out of my excel sheet. I know a bit of HTML syntax but a newbie to VBA. I'm guessing the formatting has to be done in the Emailbody text function in my code.
I'm also trying to understand the logic behind the code and also learning excel vba bit by bit at the minute.