Most people think an Excel workbook with an XLS file extension has only 65,636 rows and 256 columns in the worksheet. They would be wrong. Of course, if you don’t use a Mac there’s no way for you to discover this fact. When you open an Excel 97-2003 Workbook (.xls) file in Excel 2007 or 2010 (Windows) it’s done in Compatibility Mode, which you can see at the top of the Excel window.
I put together a few to show the size of the worksheet, the Excel version, Operating System version, and System info. This information is from an XLS file opened in Excel 2010 (Windows) in Compatibility Mode. The formulas I used are:.
Rows =ROWS(A:A). Columns =COLUMNS(1:1). Last Cell Address =ADDRESS(Rows,Columns,4). Excel Release =INFO(“RELEASE”).
OS Version =INFO(“OSVERSION”). System =INFO(“SYSTEM”) XLS Files in Excel for Mac Excel for Mac versions 2008 and 2011 do not open XLS files in this “Compatibility Mode” and the row and column limitations do not exist.
The Microsoft Excel's Header/Footer command can help you show information in the header or footer at the top or bottom of each printed page easily. And you can also add some images in the header, such as your company’s logo, etc. I am trying to insert an object/picture into an Excel file. It is showing up huge in the middle of my document, even when I insert in header.
What this means is that when you open an XLS file in Excel 2011 or 2008 (Mac) you get the newer, larger spreadsheet size. The information above is from the same file I opened in Excel 2010 (Windows). As you can see there are 1,048,576 rows and 16,384 columns in this XLS file. Excel for Mac Compatibility I made a change to the worksheet by typing “test” into cell E4 and saved the file with no problem. Then I typed “test” into cell A65537, which is one row larger than the XLS format permits. When I tried to save the file, the following message appeared. Which means I should save the file in the new XLSX format to keep my changes.
Excel for Mac has preferences for Compatibility. Choose Excel Preferences Compatibility. However nothing will change the fact that if you put data outside the 65,536 row and 256 column limitation for XLS files, Excel will warn you to save the file in the new XLSX format. It’s just another variation between Excel for Windows and Excel for Mac.
Insert Picture Using VBA Code Basically, there are two methods to insert a picture in Excel Sheet Method 1. ActiveSheet.Pictures.Insert Method 2. ActiveSheet.Shapes.AddPicture VBA Code for Inserting Picture in Excel Sheet Method 1 Using.Pictures.Insert method, you can insert a picture in Active sheet. Let see the Syntax of this method: Syntax of.Pictures.Insert Method ActiveSheet.Pictures.Insert(‘Picture URL’) This function requires only one parameter – Full path of the picture to be inserted in Excel Sheet. This is a mandatory parameter here. For Example: ActiveSheet.Pictures.Insert(“C:.
MyPic.jpg”) Above statement will simply insert myPic.jpg picture in Active sheet in its original Size. If you want to resize and locate the picture according to you, then use the following statement to resize the image and place it where ever you want in the excel sheet. VBA Code to re-size (height and width) the inserted picture Below code will set the height and width of the selected picture in worksheet which is inserted using VBA code: With ActiveSheet.Pictures.Insert('Picture full path').Left = 110.Top = 220.Width = 123.Height = 134 End With Explanation and issues with above Code Left and Top will be set without any issue. Later, Width of the image will be set to 123 as specified – Height of the image will be automatically set to a respective height to the width – because AspectRatio of the image is by default set to True Similarly when control goes to the next statement then it will reset the height to 134 and since, aspect ratio is false, width will be adjusted to new respective value. Challenge here is that you can NOT set AspectRatio flag of the picture while inserting it. (by above statement) Therefore, be careful while resizing the picture while inserting it by using the above code So what is the solution?
Here is the solution 1. First add the picture in its own size. Store the name of this image (uniquely generated one) in a variable. So that you can refer this picture uniquely later on 3. Using this variable, select that Shape and set the aspect ratio to false 4.
Then set the height and width of the picture. Here is the code now Dim nameOfPicture as String With ActiveSheet.Pictures.Insert('Picture file full path').Left = ActiveSheet.Range('photograph').Left + 2.Top = ActiveSheet.Range('photograph').Top + 2 nameOfPicture=.Name End With ActiveSheet.Pictures(profile).Select With Selection.ShapeRange.LockAspectRatio = msoFalse.Width = 123.Height = 134 End With 2. VBA Code to set the location of the inserted Picture Here you can either set fixed Left and Top value where you want to place your picture. In this case no matter what is the height and width of the cell in the worksheet, your picture will be always placed at a specific location. But suppose if you want – your picture should always be placed at a specific row and column then you can set the left and top values as follows: With ActiveSheet.Pictures.Insert.Left = ActiveSheet.Range('A1').Left.Top = ActiveSheet.Range('A1').Top.Placement = 1 End With Now your selected picture will always be placed where Column A1 starts from left and Row 1 starts from top. It means even if you change height or width of the Range A1, your picture is always going to be in Range A1 only.
This method, simply links the image in to your Excel Sheet. It means, after inserting a picture, using this method, if you send it to another computer, picture will not be displayed and an Error message be displayed.
Therefore, this method is good only when you are going to use this excel sheet always in your own computer. VBA Code for Embedding Picture in Excel Sheet Method 2 Using.Shapes.AddPicture method, you can insert a picture in Active sheet. This method overcome the challenges of above method. This allows user to Embed the picture with the Excel Workbook itself. It means, even if you share the workbook to other computer this picture will go with the document and you will be able to see it in other computer as well. Syntax of.Shapes.AddPicture Method.Shapes.AddPicture( Filename, LinkToFile, SaveWithDocument, Left, Top, Width, Height ) Where: Filename: (Mandatory) As the names suggests, this is the complete file path of the picture you want to embed to your Excel Sheet LinkToFile: (Mandatory) MsoTriState- True or False – To set whether you want to create a link to the file? SaveWithDocument: (Mandatory) MsoTriState – True or False – This is the flag which needs to be set to TRUE to embed the picture with Excel Sheet.
Left: (Mandatory)The position of the upper-left corner of the picture with respect to the upper-left corner of the document. Top: (Mandatory) The position (in points) of the upper-left corner of the picture with respect to the top of the document. Width: (Mandatory) The width of the picture you want to set. To keep the picture in its original width provide -1 Height: (Mandatory) The Height of the picture you want to set.
To keep the picture in its original Height provide -1 Example: Following VBA code will Embed this picture with the Excel file and it will display in any computer you sent it. ActiveSheet.Shapes.AddPicture Filename:='full path of your file with extension'linktofile:=msoFalse, savewithdocument:=msoCTrueLeft:=50, Top:=50, Width:=250, Height:=250. ‘as long as all of you cells have a value you could try this. Can you help me update this code so i dont get error message upon hitting cancel when trying to select an image? I get a debug on filters.clear??
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |