LesleyClark
Board Regular
- Joined
- Feb 15, 2010
- Messages
- 122
Dear All
I sent a Workbook with the below macro (digitally signed) to a friend, who has Excel 2003, which he downloaded.
When he runs the macro button, and hits Yes at the message box point, it then proceeds to perform a "file conversion". Whenever he downloads my spreadsheets to his documents a "file conversion" takes place (he has 2003 and I have 2007). However, this has never occurred during the running of the macro.
Then nothing else happens. It neither saves a file copy as per the coding, nor deletes the values in the Database.
The macro works fine on my system and I have run the compatibility checker.
Can anyone help with this please?
Thanks in advance
Lesley
I sent a Workbook with the below macro (digitally signed) to a friend, who has Excel 2003, which he downloaded.
When he runs the macro button, and hits Yes at the message box point, it then proceeds to perform a "file conversion". Whenever he downloads my spreadsheets to his documents a "file conversion" takes place (he has 2003 and I have 2007). However, this has never occurred during the running of the macro.
Then nothing else happens. It neither saves a file copy as per the coding, nor deletes the values in the Database.
The macro works fine on my system and I have run the compatibility checker.
Can anyone help with this please?
Code:
Sub ArchiveWorkbook()
Dim NewWorkBook As String
Dim CurrentWorkBook As String
Dim TheDate As String
Dim sh As Worksheet
Dim Finalrow As Integer, Finalcolumn As Integer
Dim MyMsg As String
Dim MyTitle As String
Dim Response As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
TheDate = Format(Date, "d-m-yyyy")
NewWorkBook = ActiveWorkbook.Path & "\" & "FG_Archived_On " & TheDate & ".xlsm"
CurrentWorkBook = "FG_Version_Current.xlsm"
MyTitle = "Year End Procedure"
MyMsg = "This action will clear all values from the Database. Are you sure you wish to continue?"
Response = MsgBox(MyMsg, vbExclamation + vbYesNo, MyTitle)
Select Case Response
Case Is = vbNo
Exit Sub
Case Is = vbYes
ActiveWorkbook.SaveAs Filename:=NewWorkBook
Workbooks(NewWorkBook).Save
Workbooks(NewWorkBook).Close
Workbooks.Open (CurrentWorkBook)
Worksheets("Database").Open
Set sh = Workbooks(CurrentWorkBook).Worksheets("Database")
Finalrow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
Finalcolumn = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column
sh.Cells(2, 2).Resize(Finalrow, Finalcolumn).Delete
Workbooks(CurrentWorkBook).Save
End Select
End Sub
Thanks in advance
Lesley