Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Formatting in an html excel file Results

User Interface
New User Interface
The new results-oriented user interface makes it easy for you to work in Microsoft Office Excel. Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with dropdown galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option.
No matter what activity you are performing in the new user interface, whether its formatting or analyzing data, Excel presents the tools that are most useful to successfully complete that task.
Introducing the new interface
There is a new look for Office Excel 2007, a new user interface (UI) that replaces menus, toolbars, and most of the task panes from previous versions of Excel with a single mechanism that is simple and apparent. The new user interface is designed to help you be more productive in Excel, more easily find the right features for various tasks, discover new functionality, and be more efficient.
The New Interface commands hierarchy:
1. Menu Tabs.
2. Ribbons.
3. Groups within each tab break a task into subtasks.
4. Command buttons (icons) in each group carry out a command or display a menu of commands.
Ribbon user interface: The primary replacement for menus and toolbars in Office Excel 2007 is the Ribbon. Designed for easy browsing, the Ribbon consists of tabs that are organized around specific scenarios or objects. The controls on each tab are further organized into several groups. The Ribbon can host richer content than menus and toolbars can, including buttons, galleries, and dialog box content.
Tabs that appear only when you need them: In addition to the standard set of tabs that you see on the Ribbon whenever you start Office Excel 2007, there are two other kinds of tabs, which appear in the interface and are useful for the type of task you are currently performing.
Contextual tools: Contextual tools enable you to work with an object that you select on the page, such as a table, a picture, or a drawing. When you click the object, the pertinent set of contextual tabs appears in an accent color next to the standard tabs.
Program tabs: Program tabs replace the standard set of tabs when you switch to certain authoring modes or views, including Print Preview.
File Button : This button is located in the upper-left corner of the Excel window and opens the menu shown here:

Quick Access Toolbar: The Quick Access Toolbar is located by default at the top of the Excel window and provides quick access to tools that you use frequently. You can customize the Quick Access Toolbar by adding commands to it.
Adding Commands to Quick Access Toolbar: In the Customize Quick Access Toolbar box, select either For all documents (as a default) or a specific document.
Click the command that you want to add, and then click Add.

Dialog Box Launchers: Dialog Box Launchers are small icons that appear in some groups. Clicking a Dialog Box Launcher opens a related dialog box or a task pane, providing more options related to that group.

Use the Keyboard to Access any Commands in the Ribbon
To use keyboard shortcut: To open a menu tab, press the Alt tab, now press a letter(s) or a number or a combination of a letter & a number , see below:
Step 1: press the Alt key or F10.
Step 2:
Press H, and then a letter(s) or a number or a combination of both (a letter & a number).
Or
Use the Tab key to move between command buttons in the Ribbon.
Memory management, Workbook, Worksheet & Cells
Memory Management
Memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.
You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports dual-processors and multithreaded chipsets.
Numbers of Rows, Columns & Cells in a Worksheet
Excel 2007 sheet contains 1,048,576 rows by 16,384 columns, total of 17,180,033,024 cells compare to previous Excel versions which hold 65,536 rows by 256 columns, total of 16,777,216 cells.
New file formats
XML-based file format: In 2007 Microsoft Office system, Microsoft is introducing new files formats for Word, Excel, and PowerPoint, known as the Microsoft Office Open XML formats. These new file formats facilitate integration with external data sources, and also offer reduced file sizes and improved data recovery. In Excel 2007, the default format for an Excel workbook is the Office Excel 2007 XML-based file format (.xlsx). Other available XML-based formats are the Excel 2007 XML-based and macro-enabled file format (.xlsm), the Excel 2007 file format for an Excel template (.xltx), and the Excel 2007 macro-enabled file format for an Excel template (.xltm).
Themes, Colors & Formatting
Office themes
In Office Excel 2007, you can quickly format the data in your worksheet by applying a theme and by using a specific style. Themes can be shared across other 2007 Office release applications, such as Microsoft Office Word and Microsoft Office PowerPoint, while styles are designed to change the format of Excel-specific items, such as Excel tables, charts, PivotTables, shapes, or diagrams.
Number of Colors
Excel 2007 supports up to 16 million colors.
Rich conditional formatting
You can implement and manage multiple Conditional Formatting rules that apply rich visual formatting in the form of gradient colors, data bars, and icon sets to data that meets those rules. Conditional formats are also easy to apply in just a few clicks, you can see relationships in your data that you can use for your analysis purposes.
Formulas & Functions
Easy formula writing
Resizable formula bar: The formula bar automatically resizes to accommodate long, complex formulas, which prevents the formulas from covering other data in your worksheet. You can also write longer formulas with more levels of nesting than you could in earlier versions of Excel.
Function AutoComplete: With Function AutoComplete, you can quickly write the proper formula syntax. From easily detecting the functions that you want to use, to getting help completing the formula arguments, you will be able to get formulas right the first time and every time.
Easy access to Named ranges: By using Name manager, you can organize, update, and manage multiple Named ranges in a central location, which helps all users who need to work on your worksheet interpret its formulas and data.
New Functions
Very important and useful functions are added to Excel 2007. The functions are IFERROR, AVERAGEIF, AVERAGEIFS, SUMIFS and COUNTIFS. Read more and see example in Chapter 9, page 155.
New OLAP formulas and cube functions
When you work with multidimensional databases (such as SQL Server Analysis Services) in Excel 2007, you can use OLAP formulas to build complex, free form, OLAP data bound reports. New cube functions are used to extract OLAP data (sets and values) from Analysis Services and display it in a cell. OLAP formulas can be generated when you convert PivotTable formulas to cell formulas or when you use AutoComplete for cube function arguments when you type formulas.
Charts
A New look of charts
You can use new charting tools to easily create professional-looking charts that communicate information effectively. Based on the theme that is applied to your workbook, the new, up-to-date look for charts includes special effects, such as 3-D, transparency, and soft shadows.
The new user interface makes it easy to explore the available chart types so that you can create the right chart for your data. Numerous predefined chart styles and layouts are provided so that you can quickly apply a good-looking format and include the details that you want in your chart.
Visual chart element pickers: Beside the quick layouts and quick formats, you can now use the new user interface to quickly change any element of the chart to best present your data. In a few clicks, you can add or remove titles, legends, data labels, trendlines, and other chart elements.
A modern look with OfficeArt: Since charts in Excel 2007 are drawn with OfficeArt, almost everything you can do to an OfficeArt shape can also be done to a chart and its elements. For example, you can add a soft shadow or a bevel effect to make an element to stand out or use transparency to make elements visible that are partially hidden in a chart layout. You can also use realistic 3-D effects.
Clear lines and fonts: Lines in charts appear less jagged, and ClearType fonts are used for text to improve readability.
More colors than ever: You can easily choose from the predefined theme colors and vary their color intensity. For more control, you can also add your own colors by choosing from 16 million colors in the Colors dialog box.
Chart templates: Saving your favorite charts as a chart template is much easier in the new user interface.
Shared charting
Using Excel charts in other applications: In Excel 2007, charting is shared between Excel, Word, and PowerPoint. Rather than using the charting features that are provided by Microsoft Graph, Word and PowerPoint now incorporate the powerful charting features of Excel. Since an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides the rich functionality of Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents.
Copying charts to other applications: Charts can be easily copied and pasted between documents or from one application to another. When you copy a chart from Excel to Word or PowerPoint, it automatically changes to match the Word document or PowerPoint presentation, but you can also retain the Excel chart format. The Excel worksheet data can be embedded in the Word document or PowerPoint presentation, but you can also leave it in the Excel source file.
Animating charts in PowerPoint: In PowerPoint, you can more easily use animation to emphasize data in an Excel-based chart. You can animate the entire chart or the legend entry and axis labels. In a column chart, you can even animate individual columns to better illustrate a specific point. Animation features are easier to find and give you much more control. For example, you can make changes to individual animation steps, and use more animation effects.
Sorting, Filtering & Tables
Improved sorting and filtering
You can now sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter dropdown list, select multiple items to filter, and filter data in PivotTables.
Excel table enhancements
You can use the new user interface to quickly create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet so that its much easier to work with.
PivotTables
Easy-to-use PivotTables
By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away. You no longer have to drag data to drop zones that arent always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.
After you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.
Sharing & Connections
New ways to share your work
Using Excel Services to share your work: If you have access to Excel Services, you can use it to share your Office Excel 2007 worksheet data with other users, such as executives and other stakeholders in your organization. In Excel 2007, you can save a workbook to Excel Services and specify the worksheet data that you want other people to see. In a browser (browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Microsoft Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), they can then use Microsoft Office Excel Web Access to view, analyze, print, and extract this worksheet data. They can also create a static snapshot of the data at regular intervals or on demand. Excel Web Access makes it easy to perform activities, such as scrolling, filtering, sorting, viewing charts, and using drill-down in PivotTables. You can also connect the Excel Web Access Web Part to other Web Parts to display data in alternative ways. And with the right permissions, Excel Web Access users can open a workbook in Excel 2007 so that they can use the full power of Excel to analyze and work with the data on their own computers if they have Excel installed.
Using this method to share your work ensures that other users have access to one version of the data in one location, which you can keep current with the latest details. If you need other users, such as team members, to supply you with comments and updated information, you may want to share a workbook the same way.
Quick connections to external data
You no longer need to know the server or database names of corporate data sources. Instead, you can use Quick Launch to select from a list of data sources that your administrator or workgroup expert has made available for you. A connection manager in Excel allows you to view all connections in a workbook and make it easier to reuse a connection or to substitute a connection with another user.
Printing
Better printing experience
Page Layout View: In addition to the Normal view and Page Break Preview view, Excel 2007 provides a Page Layout View. You can use this view to create a worksheet while keeping an eye on how it will look in printed format. In this view, you can work with page headers, footers, and margin settings right in the worksheet, and place objects, such as charts or shapes, exactly where you want them to be. You also have easy access to all page setup options on the Page Layout tab in the new user interface so that you can quickly specify options, such as page orientation. Its easy to see what will be printed on every page, which will help you avoid multiple printing attempts and truncated data in printouts.
Saving to PDF and XPS format: Like other 2007 Office release applications, Excel 2007 supports saving a workbook to a high-fidelity fixed file format, such as Portable Document Format (PDF) or XML Paper Specification (XPS) format, that encapsulates how it will look when it is printed. This allows you to share the content of your workbook in a format that is easy for other people to view online or print, without including the underlying formulas, external data queries, or comments.
Actually, this is what you did in earlier versions of Excel to collect the information you need before you save it to Excel Services.
Using Document Management Server: Excel Services can be integrated with Document Management Server to create a validation process around new Excel reports and workbook calculation workflow actions, such as a cell-based notification or a workflow process based on a complex Excel calculation. You can also use Document Management Server to schedule nightly recalculation of a complex workbook model.

Hello Team,

I have the following code which once I have selected a range of data, it will automatically convert the data range to Html then copy to outlook. All of this is working fine but the main problem is that (1) the script is not copying all selected information, i.e if a cell is loaded with info, it will only copy the info within the cell default size and (2) if I have an image input in the sheet, it will not copy it. Note that the company logo (image) is located in cell A1


	VB:
	
 
Sub Mail_2() 
     ' Don't forget to copy the function RangetoHTML in the module.
     ' Working in Office 2000-2010
    Application.DisplayAlerts = False 
     'Application.ScreenUpdating = False
    Dim rng As Range 
    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim Liste_de_diffusion As String 
    Dim Liste_de_copy As String 
    Dim Subject As String 
    Dim Myname 
     
    Const olFormatHTML = 2 
    Liste_de_diffusion = ActiveSheet.Range("C10").Value 
    Liste_de_copy = ActiveSheet.Range("C11").Value 
    Subject = ActiveSheet.Range("D10").Value 
    Myname = Format(Now(), "mm/dd/yy") 
     
    Set rng = Nothing 
    On Error Resume Next 
     'Only the visible cells in the selection
    Set rng = Selection.SpecialCells(xlCellTypeVisible) 
     'You can also use a range if you want
     'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
    On Error Goto 0 
     
    If rng Is Nothing Then 
        MsgBox "The selection is not a range or the sheet is protected" & _ 
        vbNewLine & "please correct and try again.", vbOKOnly 
        Exit Sub 
    End If 
     
    With Application 
        .EnableEvents = False 
        .ScreenUpdating = False 
    End With 
     
    Set OutApp = CreateObject("Outlook.Application") 
    Set OutMail = OutApp.CreateItem(0) 
     
    On Error Resume Next 
    With OutMail 
        .From = "ZZZ ECS INCIDENT GDF SUEZ" 
        .To = Liste_de_diffusion 
        .CC = Liste_de_copy 
        .BCC = "sdksdjskdjskdjsjd" 
        .Subject = Subject & " " & Myname 
        .BodyFormat = olFormatHTML 
        .HTMLBody = RangetoHTML(rng) 
        .Attachments.Add ("C:Ticket Backlog Status & Log.rar") 
        .Send 'or use .Display
         
         
    End With 
    On Error Goto 0 
     
    With Application 
        .EnableEvents = True 
        .ScreenUpdating = True 
    End With 
     
    Set OutMail = Nothing 
    Set OutApp = Nothing 
End Sub 
 
Function RangetoHTML(rng As Range) 
     ' Changed by Ron de Bruin 28-Oct-2006
     ' Working in Office 2000-2010
    Dim fso As Object 
    Dim ts As Object 
    Dim TempFile As String 
    Dim TempWB As Workbook 
     
     
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
     
     'Copy the range and create a new workbook to past the data in
    rng.Copy 
    Set TempWB = Workbooks.Add(1) 
    With TempWB.Sheets(1) 
        .Cells(1).PasteSpecial Paste:=8 
        .Cells(1).PasteSpecial xlPasteValues, , False, False 
        .Cells(1).PasteSpecial xlPasteFormats, , False, False 
        .Cells(1).Select 
        Application.CutCopyMode = False 
        On Error Resume Next 
        .DrawingObjects.Visible = True 
        .DrawingObjects.Delete 
        On Error Goto 0 
    End With 
     
     'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _ 
        SourceType:=xlSourceRange, _ 
        Filename:=TempFile, _ 
        Sheet:=TempWB.Sheets(1).Name, _ 
        Source:=TempWB.Sheets(1).UsedRange.Address, _ 
        HtmlType:=xlHtmlStatic) 
        .Publish (True) 
    End With 
     
     'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
    RangetoHTML = ts.ReadAll 
    ts.Close 
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ 
    "align=left x:publishsource=") 
     
     'Close TempWB
    TempWB.Close savechanges:=False 
     
     'Delete the htm file we used in this function
    Kill TempFile 
     
    Set ts = Nothing 
    Set fso = Nothing 
    Set TempWB = Nothing 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Attached is a sample of both mail an excel workbook.

Thanks,Attachment.rar

Jeremie

Hello Team,

Could you please help me customizing the below code which will allow me to include the object as well when sending a mail? for the time being, it only copies my visible cells (not object nor graph), convert to html and send/display as outlook mail.


	VB:
	
 
Sub Mail_2() 
     ' Don't forget to copy the function RangetoHTML in the module.
     ' Working in Office 2000-2010
    Application.DisplayAlerts = False 
     'Application.ScreenUpdating = False
    Dim rng As Range 
    Dim OutApp As Object 
    Dim OutMail As Object 
    Dim Liste_de_diffusion As String 
    Dim Liste_de_copy As String 
    Dim Subject As String 
    Dim Myname 
     
    Const olFormatHTML = 2 
    Liste_de_diffusion = ActiveSheet.Range("C10").Value 
    Liste_de_copy = ActiveSheet.Range("C11").Value 
    Subject = ActiveSheet.Range("D10").Value 
    Myname = Format(Now(), "mm/dd/yy") 
     
    Set rng = Nothing 
    On Error Resume Next 
     'Only the visible cells in the selection
    Set rng = Selection.SpecialCells(xlCellTypeVisible) 
     'You can also use a range if you want
     'Set rng = Sheets("YourSheet").Range("D4:D12").SpecialCells(xlCellTypeVisible)
    On Error Goto 0 
     
    If rng Is Nothing Then 
        MsgBox "The selection is not a range or the sheet is protected" & _ 
        vbNewLine & "please correct and try again.", vbOKOnly 
        Exit Sub 
    End If 
     
    With Application 
        .EnableEvents = False 
        .ScreenUpdating = False 
    End With 
     
    Set OutApp = CreateObject("Outlook.Application") 
    Set OutMail = OutApp.CreateItem(0) 
     
    On Error Resume Next 
    With OutMail 
        .To = Liste_de_diffusion 
        .CC = Liste_de_copy 
        .BCC = "[EMAIL="jeremie.xxxxxx@xxxxxx.com"]jeremie.xxxxxx@xxxxxx.com[/EMAIL]" 
        .Subject = Subject & " " & Myname 
        .BodyFormat = olFormatHTML 
        .HTMLBody = RangetoHTML(rng) 
        .Attachments.Add ("C:Ticket Backlog Status & Log.rar") 
         '.Send
        .Display 
         
         
    End With 
    On Error Goto 0 
     
    With Application 
        .EnableEvents = True 
        .ScreenUpdating = True 
    End With 
     
    Set OutMail = Nothing 
    Set OutApp = Nothing 
End Sub 
 
Function RangetoHTML(rng As Range) 
     ' Changed by Ron de Bruin 28-Oct-2006
     ' Working in Office 2000-2010
    Dim fso As Object 
    Dim ts As Object 
    Dim TempFile As String 
    Dim TempWB As Workbook 
     
     
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" 
     
     'Copy the range and create a new workbook to past the data in
    rng.Copy 
    Set TempWB = Workbooks.Add(1) 
    With TempWB.Sheets(1) 
        .Cells(1).PasteSpecial Paste:=8 
        .Cells(1).PasteSpecial xlPasteValues, , False, False 
        .Cells(1).PasteSpecial xlPasteFormats, , False, False 
        .Cells(1).Select 
        Application.CutCopyMode = False 
        On Error Resume Next 
        .DrawingObjects.Visible = True 
        .DrawingObjects.Delete 
        On Error Goto 0 
    End With 
     
     'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _ 
        SourceType:=xlSourceRange, _ 
        Filename:=TempFile, _ 
        Sheet:=TempWB.Sheets(1).Name, _ 
        Source:=TempWB.Sheets(1).UsedRange.Address, _ 
        HtmlType:=xlHtmlStatic) 
        .Publish (True) 
    End With 
     
     'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) 
    RangetoHTML = ts.ReadAll 
    ts.Close 
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ 
    "align=left x:publishsource=") 
     
     'Close TempWB
    TempWB.Close savechanges:=False 
     
     'Delete the htm file we used in this function
    Kill TempFile 
     
    Set ts = Nothing 
    Set fso = Nothing 
    Set TempWB = Nothing 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,

Jeremie.

Ok, I was recently given a file with lots of formulas and macros to aid me in my shipping process for my eBay business. I received the file and updated the folder destinations to reflect my own. (in the vba editor). Now, it wont let me change the source file to reflect the needed csv file that I need to reference. It only allows the source to be either an excel spreadsheet, and html file, or Lotus 123. I need the source to be a csv file. I'm sure that there is an easy way around this, I just can't figure it out. I know that I have to edit links but it won't point to any csv files. (a particular csv file that I need to source for this excel spreadsheet)

I have a bit of code that converts an excel table into HTML code via the use of MSWord. The problem I am having is that it appears Excel doesn't recognize:

	VB:
	
, LineEnding:=wdCRLF 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code will save the file as "HTML-Test.html" in the correct location, but not in the appropriate text format, which means I have some rediculous syntax errors going on above and below my table.

Is there an alternate way to accomplish what I'm trying to do, or maybe some functions I am unaware of?

The sub is below, along with a copy of the workbook with an example table.


	VB:
	
 Convert_to_HTML_Click() 
    Dim objWordApp As Object 
    Dim objWordDoc As Object 
    Dim rngData As Range 
    Dim myrange As Range 
    Dim saveloc 
     
    saveloc = ActiveWorkbook.Path 
     
     'find last row/column and define the data range
    lastrow = Sheet1.Range("A65535").End(xlUp).Row 
    lastcol = Sheet1.Range("IV1").End(xlToLeft).Column 
    Set rngData = Range(Sheet1.Cells(1, 1), Sheet1.Cells(lastrow, lastcol)) 
     
     
     '' create instance of word and open a blank doc
    Set objWordApp = CreateObject("Word.application") 
    objWordApp.Visible = True 
    Set objWordDoc = objWordApp.Documents.Add(Template:="Normal", NewTemplate:=False, DocumentType:=0) 
     
     '' create HTML table code
    objWordApp.Selection.typetext Text:="" 
    objWordApp.Selection.TypeParagraph 
    objWordApp.Selection.TypeParagraph 
     
    For Each myrange In rngData 
        iVal = myrange.Value 
        If myrange.Value = "" Then 
            iVal = " " 
        Else 
            sstring = Replace(iVal, " ", "+") 
        End If 
        If myrange.Column = 1 Then 
            If myrange.Value = Sheet1.Range("A1").Value Then 
                objWordApp.Selection.typetext Text:="" & iVal & "" 
                objWordApp.Selection.TypeParagraph 
            Else 
                objWordApp.Selection.typetext Text:="" & iVal & "" 
                objWordApp.Selection.TypeParagraph 
            End If 
        ElseIf myrange.Column = lastcol Then 
            objWordApp.Selection.typetext Text:="" & iVal & "" 
            objWordApp.Selection.TypeParagraph 
            objWordApp.Selection.TypeParagraph 
        Else 
            objWordApp.Selection.typetext Text:="" & iVal & "" 
            objWordApp.Selection.TypeParagraph 
        End If 
    Next 
    objWordApp.Selection.typetext Text:="" 
     
     '' Save output to .HTML text doc and close word
    objWordApp.ChangeFileOpenDirectory (saveloc) 
    objWordDoc.SaveAs Filename:="HTML-Test.html", FileFormat:=wdFormatText, Encoding:=28591, InsertLineBreaks:=True,
LineEnding:=wdCRLF 
    objWordApp.Quit 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Windows XP Prof and Excel 2003.
I have been pasting information into Excel sheets with the "Paste Special", "Text" option. I set the width of Column A to 81.71 for printing purpose and if the sentences are too long I format the cell to "Merge Cells" and "Wrap Text". Always worked until I entered the following macro into one of the workbooks.
#
Sub GetSinclTextFile()
Application.ScreenUpdating = False
Workbooks.OpenText Filename:="C:SINCL.TXT", Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=True, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, _
Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)) _
, TrailingMinusNumbers:=True
Columns("A:A").Delete Shift:=xlToLeft
Rows("1:12").Delete Shift:=xlUp
ActiveSheet.UsedRange.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("J65536").End(xlUp).Offset(1, -9).Resize(numRows + 100, numColumns + 10).ClearContents
Range("A65536").End(xlUp).Select
Application.ScreenUpdating = True
End Sub
/#
When I want to paste text as before, it now pastes it one word in each cell, going across the columns. Not like before, the whole sentence into one cell. It does not make a difference if I use a new workbook or an existing workbook. The only way it will accept the text in a single cell is if I use the "Paste Special", "HTML" option. It seemed to have changed the Excel setup but I can't find where I could change this back to the previous setting. I assume it is because of the above macro. Could anyone help me with expanding the macro so it returns the settings to normal.
Thanks in advance
John

Hi,

I am trying to generate html files using data on an excel sheet. I have had quite a lot of success with this in the past using macros and functions etc. But in the past the data used to create an html file has always been in one row. eg 100 rows of data would create 100 html files. The excel datasheet contains data on paintings eg "painting name", "size", "artist" "image link" which is then all combined into an html file. Each row of data is about a different painting...


	VB:
	
html file 1: painting_row1 & "by" & artist_data_row1 "size:" size_data_row1 
html file 2: painting_row2 & "by" & artist_data_row2 "size:" size_data_row2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I am trying to do not is create an html file that uses data from several rows. The best way to explain the problem I am having is to explain the desired end product. I want an html file with data about several "paintings".

eg:


	VB:
	
Html file: artist_data_row1 & "painted" & painting_row1 & "and" & painting_row6 & "and" & painting_row34 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

So if an Artist has painted 23 paintings then I want to gather the data from the relevant rows. I am not sure how to do this.

More confusing for me is that I am not sure how to limit the data being gathered eg. If there are 23 paintings by an artist I only want data from 10. I would prefer the 10 to be randomly selected from the 23 but that is not essential.

Can I do stuff like this with functions and macros in excel or do I need to start programming?

Thanks, James

Hi,

I am putting together an email survey, basically the responses are input into a html form and returned in an email attachment (postdata.att) and i am using the following code to import the attachments in turn into excel.

Unfortunately the data in postdata.att is along the lines of:
firstname=(response) eg. firstname=dave
receive updates=(response)

so when I use the macro below to import it into excel i end up with "firstname=(response) in the cells when I really only want the response, is there any way I can alter the macro below to remove the stuff before the = bit?


	VB:
	
 Outlook.Application 
Dim olNamespace As Outlook.Namespace 
Dim olFolder As Outlook.MAPIFolder 
Dim olMail As Outlook.MailItem 
Dim lngRow As Long 
Dim intAtt As Integer 
Dim wbkTemp As Workbook 
Dim strTempFile As String 
 
Set olApp = New Outlook.Application 
Set olNamespace = olApp.GetNamespace("MAPI") 
Set olFolder = olNamespace.Folders("Personal Folders") 
Set olFolder = olFolder.Folders("Comms Survey") 
 
lngRow = 2 
 ' go thru all mail in Inbox
For Each olMail In olFolder.Items 
     ' only check those with attachments
    For intAtt = 1 To olMail.Attachments.Count 
         ' only those with xls files
        If InStr(1, olMail.Attachments(intAtt).Filename, ".att", vbTextCompare) > 0 Then 
             ' get folder and filename for xls file
            strTempFile = ThisWorkbook.Path & Application.PathSeparator & olMail.Attachments(intAtt).Filename 
             ' save it so we can open and read it
            olMail.Attachments(intAtt).SaveAsFile strTempFile 
            Set wbkTemp = Workbooks.Open(strTempFile) 
            With ThisWorkbook.sheets("Survey Response Data") 
                .Cells(lngRow, 1) = olMail.SenderName 
                .Cells(lngRow, 2) = wbkTemp.sheets(1).Range("A1") 
                .Cells(lngRow, 3) = wbkTemp.sheets(1).Range("A2") 
                .Cells(lngRow, 4) = wbkTemp.sheets(1).Range("A3") 
                .Cells(lngRow, 5) = wbkTemp.sheets(1).Range("A4") 
            End With 
            lngRow = lngRow + 1 
             ' close and destroy temporary excel file
            wbkTemp.Close False 
            Set wbkTemp = Nothing 
            Kill strTempFile 
        End If 
    Next 
Next 
 
Set olMail = Nothing 
Set olFolder = Nothing 
Set olNamespace = Nothing 
Set olApp = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
End Sub

Thanks for all your help,
Wayne

Hi,

I need help on how to write a VBA code to save an specific sheet of a (multi-sheet) Excel plan with an specific name. It is part of an application that collects real time data from an industrial control system and generates daily reports. I would like to save these reports (each one is a sheet of a big excell plan) with different names for further printing. The file format can be any (.xls, .pdf, HTML, Text, etc.)

Thanks in advance

Glas

Hello,
I'm using the code below to move some html files from one folder to another. The code is working fine.

I want to edit this code so that I will move a maximum of 1500 files to the destination folder. If there are more than 1500 files to be moved, I want the macro to stop running after moving 1500 files. How do I edit this code?

Thanks in advance.


	VB:
	
 movefile() 
     'Moves required files from one folder to another
    On Error Goto errortrap 
    Dim OldName, NewName 
    With Application.FileSearch 
        .NewSearch 
        .LookIn = "C:Documents and SettingsAdministratorDesktopNew_FolderNew Folder" 
        .SearchSubFolders = True 
        .Filename = "*.htm" 
        .TextOrProperty = "a" 
        .MatchAllWordForms = True 
        .FileType = msoFileTypeAllFiles 
        If .Execute() > 0 Then 
            For I = 1 To .FoundFiles.Count 
                OldName = .FoundFiles(I) 
                NewName = "C:Documents and SettingsAdministratorDesktopNew_FolderSelangor_Non_Wildcard" & Dir(.FoundFiles(I))

                Name OldName As NewName 
            Next I 
        Else 
            MsgBox "There were no files found." 
        End If 
    End With 
errortrap: 
    Beep 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
ps. I understand that Application.FileSearch does not work with Excel 2007 anymore. Therefore, I would appreciate an alternative code which would be compatible with Excel 2007. This is not an important issue though as I am still using Excel 2003 & I've got no intention to upgrade in the near future.

Thank you once again.

need to start out that I am not very good with VBA - trying to help out one my users.

Our DBA wrote this VBA code to scan through Outlook email folder and put certain data from the emails into an excel

Here is the first part of the code. It is failing on the LAST LINE with an 'out of memory' error: Any Help or suggestions would be greatly appreciated !!!!!!!!!!!!!!!!!!!!!!!


	VB:
	
 
Dim strAttachment As String 
Dim dtStartDate As Date 
Dim dtEndDate As Date 
Dim globalRowCount As Long 
 
Dim xlApp As Excel.Application 
Dim xlBook As Excel.Workbook 
Dim xlSheet As Excel.Worksheet 
 
Option Explicit 
 
Sub Export() 
     
    Dim olApp As Outlook.Application 
    Dim olSession As Outlook.NameSpace 
    Dim olStartFolder As Outlook.MAPIFolder 
    Dim olDestFolder As Outlook.MAPIFolder 
    Dim strprompt As String 
    Dim recipient As String 
    Dim localRowCount As Integer 
     
     
    Set xlApp = CreateObject("Excel.Application") 
     
     'Initialize count of folders searched
    globalRowCount = 1 
     
     ' Get a reference to the Outlook application and session.
    Set olApp = Application 
    Set olSession = olApp.GetNamespace("MAPI") 
     
     ' Allow the user to input the start date
    strprompt = "Enter the start date to search from:" 
    dtStartDate = InputBox(strprompt, "Start Date", Now() - 7) 
     
     ' Allow the user to input the end date
    strprompt = "Enter the end date to search to:" 
    dtEndDate = InputBox(strprompt, "End Date", Now()) 
     
     ' UserForm1.Show
     
     
    If (IsNull(dtStartDate)  1) And (IsNull(dtEndDate)  1) Then 
         
         ' Allow the user to pick the folder in which to start the search.
        MsgBox ("Pick the source folder (Folder)") 
        Set olStartFolder = olSession.PickFolder 
         
         ' Check to make sure user didn't cancel PickFolder dialog.
        If Not (olStartFolder Is Nothing) Then 
             ' Start the search process.
            ProcessFolder olStartFolder 
            MsgBox CStr(globalRowCount) & " messages were found." 
        End If 
         
        xlApp.Quit 
         
         ' strprompt = "Enter the recipient of the .html attachment in xxx@xxx.xxx format: "
         ' recipient = InputBox(strprompt, "Recipient's email", "oln_dba@myemail.com")
         
         ' DTSMailer strMessageBody, strAttachment
         ' DTSMailer commented out b/c no DTS package reference available on Geeta's machine.
         
         ' MsgBox "Email sent to " & recipient
        MsgBox "Process is complete. Check K:folderhtm for available files." 
         
    End If 
End Sub 
 
Sub ProcessFolder(CurrentFolder As Outlook.MAPIFolder) 
     
    Dim i As Long 
    Dim ValidEmails As Long 
    ValidEmails = 0 
     
    For i = CurrentFolder.Items.Count To 1 Step -1 
        If ((CurrentFolder.Items(i).ReceivedTime >= dtStartDate) And (CurrentFolder.Items(i).ReceivedTime < dtEndDate)) Then 
            ValidEmails = ValidEmails + 1 
        End If 
    Next 
     
    If CurrentFolder.Items.Count >= 1 And ValidEmails >= 1 Then 
         
        Dim localRowCount As Integer 
        Dim xlName As String 
         
        Set xlBook = xlApp.Workbooks.Add 
        Set xlSheet = xlBook.Worksheets(1) 
         
        localRowCount = 1 
        xlName = CStr(Format(dtStartDate, "MMDDYYYY")) & "_" & CurrentFolder.Name & "_feedback" 
         
        xlSheet.Cells(localRowCount, 1) = "SUBJECT" 
        xlSheet.Cells(localRowCount, 2) = "SENDER" 
        xlSheet.Cells(localRowCount, 3) = "RECEIVED DATE" 
        xlSheet.Cells(localRowCount, 4) = "MESSAGE BODY" 
         
         
         ' Late bind this object variable,
         ' since it could be various item types
        Dim olTempItem As Object 
        Dim olNewFolder As Outlook.MAPIFolder 
         
         
         ' Loop through the items in the current folder.
         ' Looping through backwards in case items are to be deleted,
         ' as this is the proper way to delete items in a collection.
        For i = CurrentFolder.Items.Count To 1 Step -1 
             
            Set olTempItem = CurrentFolder.Items(i) 
             
             ' Check to see if a match is found
            If ((olTempItem.ReceivedTime >= dtStartDate) And (olTempItem.ReceivedTime < dtEndDate)) Then 
                localRowCount = localRowCount + 1 
                globalRowCount = globalRowCount + 1 
                xlSheet.Cells(localRowCount, 1) = olTempItem.Subject 
                xlSheet.Cells(localRowCount, 2) = olTempItem.SenderEmailAddress 
                xlSheet.Cells(localRowCount, 3) = CStr(Format(olTempItem.ReceivedTime, "MM/DD/YYYY")) 
                xlSheet.Cells(localRowCount, 4) = Replace(Replace(Replace(olTempItem.Body, Chr(9), " "), Chr(10) & Chr(10),
Chr(10)), Chr(13), "") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have done a website for a league and things such as the 'results' and 'league table' I have done as .ssi files so that they can be included at more than one place on the site (file only needs amending once and it updates everywhere)

I have also done an Excel spreadsheet where all a league rep has to do is input the results, press a button and print a page to send out to all team captains which contains the formatted results for 2 week periods and the current league table

My Question is : Can I do another button which they can press to export, in seperate files, 1) the league table, 2) the results - in a .ssi file ? And preferably forcing this to be saved under a certain name i.e. disable 'save as' (wrapping the contents of the cells in html should not be a problem)

I am trying to make this idiot proof so they can press the button and then upload the file to the website

I am sure it is possible to write to a txt file however I'm not sure I can trust them to rename and save the file correctly without mucking things up - They have managed to destroy things before !!!

Any comments/suggestions/ideas would be appreciated

Hi

I've created a combobox and filled the data with values from a named range, called "Kunden_mit_Adresse".

Code:


	VB:
	
 rngSourceKunde = Worksheets("Kunden").Range("Kunden_mit_Adresse") 
 
 'Fill the listbox
Set lbtarget = Me.lstKundenListe 
With lbtarget 
     'Determine number of columns
    .ColumnCount = 6 
     'Set column widths
    .ColumnWidths = "0;130;110;20;0;20" 
     'Insert the range of data supplied
    .List = rngSourceKunde.Cells.Value 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
As you can see on the picture/example spreadsheet, I've created several textboxes. When a value is selected from the combobox, I wish the data to be displayed in the textfields. (name, address etc). I would then like to be able to make the changes and write everything back to the workbook again.

Any suggestions? Do I need to retrieve the row where the data is stored?

Thanks

Excel File is too big as an attachment

http://www.file-upload.net/download-...ment.xlsm.html

example.jpg

I'm using the following code below to send an email via lotus notes but would like to know how to attach an excel file as well. I've tried using embed object and attachme object with no success. Any help would be appreciated. Also, I know there are other sets of code out there to attach a file, but i'd like to use the code below as it works perfectly for my needs.


	VB:
	
 Send_Excel_Cell_Content_To_Lotus_Notes() 
     'This macro does the following:
     ' A. Confirmed working on Excel 2003
     ' B. Opens Lotus Notes 6.5 or 7
     ' C. Opens a new memo message
     ' D. Copies data from the excel spreadsheet, email addresses, subject, and body
     ' E. Pastes this data as TEXT into the email
     ' F. If a user has auto signature already configured in lotus notes, this is preserved (either html or text)
     
    Dim Notes As Object 
    Dim Maildb As Object 
    Dim WorkSpace As Object 
    Dim UIdoc As Object 
    Dim UserName As String 
    Dim MailDbName As String 
     
    Set Notes = CreateObject("Notes.NotesSession") 
    UserName = Notes.UserName 
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" 
    Set Maildb = Notes.GetDataBase(vbNullString, MailDbName) 
    Set WorkSpace = CreateObject("Notes.NotesUIWorkspace") 
    Call WorkSpace.ComposeDocument(, , "Memo") 
    Set UIdoc = WorkSpace.CurrentDocument 
     
     'If cells are null, such as email address, cc, etc, then ignore and dont paste into email
    On Error Resume Next 
     
     'Copy the email address from cell C19 into the TO: field in Lotus Notes
     'Note:  Addresses in this cell should be separated by a semicolon.
     'Please change your current sheet's name from Sheet1 to your sheet's name
    Recipient = Sheets("Sheet1").Range("C19").Value 
    Call UIdoc.FieldSetText("EnterSendTo", Recipient) 
     
     'Copy the email address from cell C20 into the CC: field in Lotus Notes
     'Note:  Addresses in this cell should be separated by a semicolon
    ccRecipient = Sheets("Sheet1").Range("C20").Value 
    Call UIdoc.FieldSetText("EnterCopyTo", ccRecipient) 
     
     'Copy the email address from cell C21 into the BCC: field in Lotus Notes
     'Note:  Addresses in this cell should be separated by a semicolon
    bccRecipient = Sheets("Sheet1").Range("C21").Value 
    Call UIdoc.FieldSetText("EnterBlindCopyTo", bccRecipient) 
     
     'Copy the subject from cell C22 into the SUBJECT: field in Lotus Notes
    Subject1 = Sheets("Sheet1").Range("C22").Value 
    Call UIdoc.FieldSetText("Subject", Subject1) 
     
     'Copy the cells in the range (one column going down) into the BODY in Lotus Notes.
     'You must set the last cell C47 to one cell below the range you wish to copy.
    Call UIdoc.GotoField("Body") 
    Body1 = Replace(Join(Application.Transpose(Range([c25], [c47].End(3))), "@") & "@@Thank you,", "@", vbCrLf) 
    Call UIdoc.InsertText(Body1) 
     
     'Insert some carriage returns at the end of the email
    Call UIdoc.InsertText(vbCrLf & vbCrLf) 
    Application.CutCopyMode = False 
     
    Set UIdoc = Nothing: Set WorkSpace = Nothing 
    Set Maildb = Nothing: Set Notes = Nothing 
    Set Body = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Good afternoon all,

I've seen a few posts recently asking about sending either sending a range or a worksheet in an Outlook email as the body of the message through code. I've been looking at this and think I've come up with something that might work. I'd appreciate it if any of you XL kings and queens would take a look and see if the code works OK on your machine. I've sent a few messages to myself (sad I know ) and they seem to work well.

Here's the code. You need to set a reference to the Outlook object Library AND the Microsoft Scripting Runtime in order for this code to work.

Any ideas for improvement, suggestions, comments gratefully received.

Dan

Code:
Option Explicit

Sub SendRange()

'Sends a specified range in an Outlook message and retains Excel formatting

'Code written by Daniel Klann 2002

'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime


'Dimension variables
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String


'Select the range to be sent
On Error Resume Next
Set rngeSend = Application.InputBox("Please select range you wish to send.", , , , , , , 8  )
If rngeSend Is Nothing Then Exit Sub    'User pressed Cancel
On Error GoTo 0

'Now create the HTML file
ActiveWorkbook.PublishObjects.Add(xlSourceRange, "C:tempsht.htm", rngeSend.Parent.Name, rngeSend.Address,
xlHtmlStatic).Publish True


'Create an instance of Outlook (or use existing instance if it already exists
Set olApp = CreateObject("Outlook.Application")

'Create a mail item
Set olMail = olApp.CreateItem(olMailItem)

'Open the HTML file using the FilesystemObject into a TextStream object
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile("C:tempsht.htm", ForReading)

'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = TStream.ReadAll

olMail.HTMLBody = strHTMLBody

olMail.Display


End Sub

[ This Message was edited by: dk on 2002-05-14 07:21 ]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"> I have an XLí07 workbook with blank payroll forms (I have and need three disparate forms) Iíve created. In addition, I have a master workbook in which I compile all my bi-monthly timesheets.
I want to copy a sheet out of the first workbook and drop it into the second, but when I do, I lose much of my cell formatting. Specifically, when I cut/copy/paste a worksheet (using either control A/control C/control V or clicking on the select all button), I lose the column/row spacingómeaning I have to resize many/all of the columns/rows. Worse, I canít seem to preserve formulas and form features (like drop down lists) when I transfer from one workbook to the next.
Can someone please suggest how to make this happen?
Disclaimer: Iím a brilliant editor, but Iím and excel idiot. Iím perfectly sure there are quick and easy answers to the above embedded within Microsoftís voluminous help references or the FAQs here. But darned if can begin to find themódonít know how to begin looking as all my search queries turn up negativeódonít know how to begin asking. If the answer to my question is on file somewhere, would much appreciate a simple finger pointing the wayÖ
Thanx in advance for your help.

I have an Excel 2003 workbook w/ >10 worksheets (Pivot Tables, 'raw text', cell-formated text, no graphics/charts/images, VB Script). The total file size when saved is >2MB.

I have an general idea of where most of the 2MB's goes within the file, but I also know that sometimes what you think isn't necessarily the reality with certain 'features' of Excel related to Pivot Table data and 'hidden' fields.

Is there any ability within Excel natively (2003 or 2007) to find out where most of the file size is 'lost'? Or even a VBA Script that could be run to return a breakdown of where memory is being used?

E.g.:
Worksheet1 = 1MB
Worksheet2 = 200k
Worksheet3 = 10k
etc...

I have this feeling that my Excel file should be closer to 1MB rather than 2MB. (I know, in this day and age of high speed networks, faster computers etc. it shouldn't be a problem... But I'd rather have a more efficient file.)

Thanks,
Dave

Ps.
I have run a couple of Macro's to help 'clean out' some of the bloat. These two scripts took my file from 3.2MB down to the current 2.3MB

Source: http://exceltips.vitalnews.com/Pages...votTables.html

	Code:
	
Sub PTReduceSize()
    Dim wks As Worksheet
    Dim PT As PivotTable

    For Each wks In ActiveWorkbook.Worksheets
        For Each PT In wks.PivotTables
            PT.RefreshTable
            PT.CacheIndex = 1
            PT.SaveData = False
        Next
    Next
End Sub
 


Excel Diet: Code:
Sub ExcelDiet()
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=83
'This will try to reduce the size of the Excel file by forcing Excel to recalculate the used range for each sheet.
     
    Dim j               As Long
    Dim k               As Long
    Dim LastRow         As Long
    Dim LastCol         As Long
    Dim ColFormula      As Range
    Dim RowFormula      As Range
    Dim ColValue        As Range
    Dim RowValue        As Range
    Dim Shp             As Shape
    Dim ws              As Worksheet
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     
    On Error Resume Next
     
    For Each ws In Worksheets
        With ws
             'Find the last used cell with a formula and value
             'Search by Columns and Rows
            On Error Resume Next
            Set ColFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set ColValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
            Set RowFormula = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            Set RowValue = .Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
            On Error GoTo 0
             
             'Determine the last column
            If ColFormula Is Nothing Then
                LastCol = 0
            Else
                LastCol = ColFormula.Column
            End If
            If Not ColValue Is Nothing Then
                LastCol = Application.WorksheetFunction.Max(LastCol, ColValue.Column)
            End If
             
             'Determine the last row
            If RowFormula Is Nothing Then
                LastRow = 0
            Else
                LastRow = RowFormula.Row
            End If
            If Not RowValue Is Nothing Then
                LastRow = Application.WorksheetFunction.Max(LastRow, RowValue.Row)
            End If
             
             'Determine if any shapes are beyond the last row and last column
            For Each Shp In .Shapes
                j = 0
                k = 0
                On Error Resume Next
                j = Shp.TopLeftCell.Row
                k = Shp.TopLeftCell.Column
                On Error GoTo 0
                If j > 0 And k > 0 Then
                    Do Until .Cells(j, k).Top > Shp.Top + Shp.Height
                        j = j + 1
                    Loop
                    If j > LastRow Then
                        LastRow = j
                    End If
                    Do Until .Cells(j, k).Left > Shp.Left + Shp.Width
                        k = k + 1
                    Loop
                    If k > LastCol Then
                        LastCol = k
                    End If
                End If
            Next
             
            .Range(Cells(1, LastCol + 1).Address & ":IV65536").Delete
            .Range(Cells(LastRow + 1, 1).Address & ":IV65536").Delete
        End With
    Next
     
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
     
End Sub
 Thanks for the help / any suggestions people may have.


Hi,
I need to automatically identify the zigzag formations in the data. I attached the example which shows the ideal and real-world-like zigzag pattern (http://rapidshare.com/files/130231085/zigzag.zip.html). Can you please help me create an automatic identification procedure in excel? It might mean making it fuzzy.
Thanking for your attention!
Dima

(I tried to find an answer on this earlier, but got no response)...

I am the webmaster for a non-profit group. We have a mySQL table and I already have a download function that works fine for dropping a CSV into Excel.

I needed to have another Excel file with some specific formatting and I came up with a brilliant (well, to me) idea to make the download as an HTML file which Excel can read. Then, to make it as easy as possible for our office person, I gave it a .CSV extension, since that file association is set to Excel on her computer.

Well, this works fine on my copy of Excel (2000) as the browser asks if I want to open the file in Excel and when I say 'yes', it opens the file and reads the HTML and voila! I have a perfectly formatted Excel spreadsheet.

But in Excel 2003...

The same thing happens, except I end up with just an HTML string inside a cell.

I have a workaround in that if she saves the file with an HTML extension and then she opens it up in Excel, it comes up formatted. But this is a person I want to give as few steps as possible (don't ask).

Is there some kind of setting that needs to get flipped to make the Excel 2003 behavior mimic my Excel 2000 behavior?

Thanks for this service, it's great!

Hello Experts,

I have searched through the threads existing about emailing ranges, and have adapted the code from the Ron de Bruin site, but can't seem to find a solution to my challenge.

I need to create an email that is a blend of a generated email (with hyperlinks) and data from an excel range (which changes in size each time). I have the code written to build the html email, and then include the RangetoHTML function. However, the email is created with just the output of the RangetoHTML function.

Any insight would be of great help.

Thanks!

Here is my code:

Sub ProcessDeploymentCommunication()
'
'Application.ScreenUpdating = False

Workbooks("LaborOpsToolbox.xls").Sheets("Deployment").Select
Range("A3:J2000").Select
Selection.ClearContents

Rows("3:3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Interior.ColorIndex = xlNone

Range("A3").Select

Dim cn As ADODB.Connection, rs As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=wdwdatastudiosdataLabor OfficeReportsLaborOpsToolboxDatabases" & "" & "LaborOpsToolboxSource.mdb"

Dim l As Long
l = frmDeploymentCommunication.ListBox1.ListCount

Dim x As Variant
Dim i As Long

For i = 0 To l - 1
If frmDeploymentCommunication.ListBox1.Selected(i) = True Then
x = frmDeploymentCommunication.ListBox1.List(i)

'Create the SQL-statement.
stSQL = "SELECT * FROM [tblDeploymentCommunication] Where [nameProp]=""" & frmDeploymentCommunication.ComboBox1.Value & """ AND [nameArea] = """ & x & """"

' open a recordset
Set rs = New ADODB.Recordset

With rs
.Open stSQL, cn, adOpenKeyset, adLockOptimistic
End With
ActiveCell.CopyFromRecordset rs

rs.Close
Set rs = Nothing

LR = ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Row
Range("A" & LR).Select

End If
Next i

cn.Close
Set cn = Nothing

Dim cell As Variant
Dim cadd As Variant
Dim cellrange As Range
Dim ChkDeploy As String

Dim r As Long

Set cellrange = ActiveSheet.Range("A3:A" & LR)

For Each cell In cellrange
cadd = cell.Address

r = Range(cadd).Row

ChkDeploy = Range(cadd).Offset(0, 7).Value

If ChkDeploy = "Yes" Then
Range("A" & r & ":J" & r).Select
Selection.Interior.ColorIndex = 36
End If

Next cell

'dropping in date

Range("A1").Select

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=wdwdatastudiosdataLabor OfficeReportsLaborOpsToolboxDatabases" & "" & "LaborOpsToolboxSource.mdb"

'Create the SQL-statement.
stSQL = "SELECT * FROM [Deployment -- Date]"

' open a recordset
Set rs = New ADODB.Recordset

With rs
.Open stSQL, cn, adOpenKeyset, adLockOptimistic
End With
ActiveCell.CopyFromRecordset rs

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

'creating email
Dim JobGroup As String
Dim Property As String
Dim WkDate As Date

Property = frmDeploymentCommunication.ComboBox1.Value
JobGroup = frmDeploymentCommunication.ComboBox2.Value
WkDate = Workbooks("LaborOpsToolbox.xls").Sheets("Deployment").Range("A1").Value

dear = "Hello,"
strbody1 = "

Please be advised that the Cast Members listed below from your area are scheduled to be deployed during week ending " & WkDate & "."
strbody2 = "

Please ensure that a Leader in your area meets with these Cast Members to give them an overview of Deployment and share the various resources that are available to them on The Hub." & _
"

If the cast member line is highlighted in yellow below, this is their first deployment experience. Please ensure that they have the proper resources available.

" & _
"Below are some helpful links to assist in this conversation:

"
Link = "First Time Cast Deployment - Leader Spiel"
Link2 = "

Resort Welcome Letters"
Link3 = "

Resort Virtual Property Tours"
Link4 = "

Transportation Assistance"
strbody3 = "

Please be advised that Cast Members can also easily access Deployment Resources from the Cast Link module on The Hub." & _
"

Thank you in advance for preparing our Cast Members for a positive Deployment experience.

"

Dim rng As Range
Dim olApp As Outlook.Application
Dim olMail As MailItem

Set rng = Nothing
On Error Resume Next
Set rng = Sheets("Deployment").Range("A1:J" & LR).SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.To = Environ("UserName")
.CC = ""
.BCC = ""
.Subject = "Deployment Communication for " & JobGroup & " at " & Property & " for week ending " & WkDate
.HTMLBody = dear & strbody1 & strbody2 & Link & Link2 & Link3 & Link4 & strbody3
.Save
End With

On Error GoTo 0

Set olMail = Nothing
Set olApp = Nothing

'Application.ScreenUpdating = True

MsgBox ("e-Mail Draft created")

Unload frmDeploymentCommunication

End Sub

Function RangetoHTML(rng As Range)
' Working in Office 2000-2007
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteFormulas
.Cells(1).PasteSpecial xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function


No luck finding an answer? You could always try Google.