Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Help
I have put together VBA code that copies data from one workbook(active) and
pastes into another workbook(destination). I am wanting to insert a statement that if the destination workbook cannot be find, run another string of code that would email the file, otherwise continue running the current code. Below is the code to copy and paste as well as the email code. Can someone please help me in using an if/then statement to meet my objective above. Sub McoSave() Application.ScreenUpdating = False Dim WBto As Workbook Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:T2" FromSheet.Range(C1).Copy Set WBto = Workbooks.Open(Filename:="F:\Jeff_H\Survey Test\Survey2.xls") Set ToSheet = WBto.Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 ToSheet.Range("A" & LastRow).PasteSpecial xlPasteValues WBto.Close SaveChanges:=True Set WBto = Nothing Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False End Sub Sub Email() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = ".com" .CC = "" .BCC = "" .Subject = "Survey" .Body = "Test" .Attachments.Add ActiveWorkbook.FullName .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Help
Sub McoSave()
Application.ScreenUpdating = False Dim WBto As Workbook Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long if dir("F:\Jeff_H\Survey Test\Survey2.xls") < "" then Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:T2" FromSheet.Range(C1).Copy Set WBto = Workbooks.Open(Filename:="F:\Jeff_H\Survey Test\Survey2.xls") Set ToSheet = WBto.Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 ToSheet.Range("A" & LastRow).PasteSpecial xlPasteValues WBto.Close SaveChanges:=True Set WBto = Nothing Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False else End if End Sub Sub Email() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = ".com" .CC = "" .BCC = "" .Subject = "Survey" .Body = "Test" .Attachments.Add ActiveWorkbook.FullName .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub -- Regards, Tom Ogilvy "hurlbut777" wrote in message ... I have put together VBA code that copies data from one workbook(active) and pastes into another workbook(destination). I am wanting to insert a statement that if the destination workbook cannot be find, run another string of code that would email the file, otherwise continue running the current code. Below is the code to copy and paste as well as the email code. Can someone please help me in using an if/then statement to meet my objective above. Sub McoSave() Application.ScreenUpdating = False Dim WBto As Workbook Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:T2" FromSheet.Range(C1).Copy Set WBto = Workbooks.Open(Filename:="F:\Jeff_H\Survey Test\Survey2.xls") Set ToSheet = WBto.Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 ToSheet.Range("A" & LastRow).PasteSpecial xlPasteValues WBto.Close SaveChanges:=True Set WBto = Nothing Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False End Sub Sub Email() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = ".com" .CC = "" .BCC = "" .Subject = "Survey" .Body = "Test" .Attachments.Add ActiveWorkbook.FullName .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Help
You could try using the Dir command, which will return an empty string if
the path&file is not found. if Len(Dir("F:\Jeff_H\Survey Test\Survey2.xls")) 0 then ' do something as the file exists else ' do as the file does not exist endif -- Cheers Nigel "hurlbut777" wrote in message ... I have put together VBA code that copies data from one workbook(active) and pastes into another workbook(destination). I am wanting to insert a statement that if the destination workbook cannot be find, run another string of code that would email the file, otherwise continue running the current code. Below is the code to copy and paste as well as the email code. Can someone please help me in using an if/then statement to meet my objective above. Sub McoSave() Application.ScreenUpdating = False Dim WBto As Workbook Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:T2" FromSheet.Range(C1).Copy Set WBto = Workbooks.Open(Filename:="F:\Jeff_H\Survey Test\Survey2.xls") Set ToSheet = WBto.Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 ToSheet.Range("A" & LastRow).PasteSpecial xlPasteValues WBto.Close SaveChanges:=True Set WBto = Nothing Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False End Sub Sub Email() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = ".com" .CC = "" .BCC = "" .Subject = "Survey" .Body = "Test" .Attachments.Add ActiveWorkbook.FullName .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|