excel 2007 xlsm format Results


Sponsored Links:



Hi,

I have noticed recently that only "xls" and "xlsx" excel formats are allowed. What happens if the 2007 workbook contains a macro to user requires and they use 2007. Must the person replying to the post save the workbook in 2003 (xls) format to retain the macro? Will this not affect the macro if it contains references to objects not available in 2003? Why is this xlsm fomat not available in the lounge?

Thought I'd just ask out of curiosity!
TX




As an example, when saving a .xlsm file in excel, the save as box restricts your view of other excel files whith the target directory to only those that end in .xlsm

Is there a way of setting the save as box so that you can see all excel files, wheher they be .xlsm, .xls, .xlsx etc




Hi all,

One of my colleagues has the macro below, she wants to modify it and enter another email address on cell L1 so the sheet get emailed to two people, what do I need to do to make it work?

Many thanks,

Sub Mail_Every_Worksheet()
'Working in 97-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
' New variables and constant
Dim wbList As Workbook
Dim shList As Worksheet
Dim r As Long
Dim strPassword As String
Dim i As Integer
Dim j As Integer
Const strChars = "ABCDEFGHIJKLMNOPQSTUVWXYZabcdefghijklmnopqrstuvwx yz0123456789"

' Initialize random generator
Randomize

TempFilePath = Environ$("temp") & ""

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

' Workbook for passwords
Set wbList = Workbooks.Add(Template:=xlWBATWorksheet)
Set shList = wbList.Worksheets(1)

For Each sh In ThisWorkbook.Worksheets
If sh.Range("K1").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
' Make password
strPassword = sh.Range("K2")

TempFileName = "Sheet " & sh.Name & " of " & _
ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum, Password:=strPassword
On Error Resume Next
.SendMail sh.Range("K1").Value, "Staff Reallocation Detail PLEASE EMAIL QUERIES - PLEASE CONTACT REGIONAL COORDINATOR TO VERBALLY RECEIVE YOUR PASSWORD"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

' Next row
r = r + 1
' E-mail address in column K cell K1
shList.Range("A" & r) = sh.Range("K1")
' Filename in column A cell A3
shList.Range("B" & r) = TempFileName & FileExtStr
' Password in column K cell K2
shList.Range("C" & r) = strPassword
End If
Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub




I am running Office 2007 with a Windows 7 Operating System. When I open/create/modify Excel files in the xls format, they always show in the Recent Files Jump List on the Taskbar (Right Click the Excel Icon). However, when I do the same with xlsx or xlsm files, it is hit or miss, usually miss. . The files do show up however as Recent Documents when I open Excel and click on the Office 7 logo at the top left. I have tried clearing the Jump List, resetting the number of files to 0, then raising it back to 10, turning off the Recent Files feature under "Properties" on the Taskbar, then turning it back on, and anything else I could think of. This issue does not occur with Word docx files which always appear on the Word Jump List.




Hi all

The macro emails each worksheet to each eamil address which is on K1, how do I make it possible to email it to the email address which is on L1 as well, see the code below

Many thanks,

Sub Mail_Every_Worksheet()
'Working in 97-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

TempFilePath = Environ$("temp") & ""

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ThisWorkbook.Worksheets
If sh.Range("K1").Value Like "?*@?*.?*" Then

sh.Copy
Set wb = ActiveWorkbook

TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail sh.Range("K1").Value, _
"Revenue Breakdown - P7"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

End If
Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub




Hello,

The segment of code below is giving me fits.

The macro is written to automatically open an existing spreadsheet, copy some cells and paste the data into the spreadsheet where the macro resides.

When I open the spreadsheet and run the macro, it runs exactly as I need without any issues.

However, if I run it a second time, the copying and pasting is all messed up. It seems to distribute the copied data across several additional columns, acting almost as a Text to Columns function.

I have to close out of Excel entirely to get it to run properly again. No issues with the first run, but subsequent runs I have problems.

[codebox]
Columns("A").Select
Selection.Copy
Application.DisplayAlerts = False
ActiveWindow.Close

Columns("A").Select
ActiveSheet.Paste[/codebox]

I tried modifications to the code to where it only selects and copies the data that is there:

[codebox] Range("A11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
[/codebox]

I've also tried copying rows instead of columns.

And I have also selected in the target sheet just the range of A1.

I've set the display alerts back to "true" after closing, and I do get an alert about the size not matching. I suspect this might have something to do with it, but so far I've not been able to pinpoint exactly where the problem is.

The spreadsheets I am copying from are in .xls format and the spreadsheet where I am pasting is a .xlsm spreadsheet.

I am using Excel 2007.

Thanks in advance for the advice!