Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if drive exists - if not prompt to SAVE AS box
Hi All,
I would really appreciate some help on this as my VB skills are not good, I can often work with code from this group,but I am stuck on this. the code below will save a copy of a worksheet to the H Drive with a file name with sheet name (paddock), cell reference and date time .xls My problem is to check if H drive exists, then if it does not exist, to prompt to SAVE As for user input to select another file on C drive with the given file name. appreciate any help on this. here is the code. Sub SaveASPaddock() 'This macro will SAVE AS to H:\ folder, which must exist before macro runs ActiveSheet.Unprotect Sheets("Paddock").Select Range("A3").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Workbooks.Add Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues Selection.PasteSpecial Paste:=xlPasteFormats Range("A1").Select Application.CutCopyMode = False ActiveWorkbook.SaveAsApplication.GetSaveAsFilename "H:\" & "PaddockFee_" & Range("D1").Value & "_" & Format(Now, "yyyymmddhhmm") & ".xls" ActiveWorkbook.Close SaveChanges:=False Sheets("Paddock").Select Range("A3").Select ActiveSheet.Protect UserInterfaceOnly:=True End Sub Thanks in advance Cheers Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if drive exists - if not prompt to SAVE AS box
You can check for the existence of a drive using the FileSystemObject... '-- Function DriveStatus(drv As String) As Boolean Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") DriveStatus = fso.DriveExists(drv) Set fso = Nothing End Function '-- So... If DriveStatus("H") = True Then 'save file Else 'chose another drive End If -- Jim Cone Portland, Oregon USA "Paul" wrote in message Hi All, I would really appreciate some help on this as my VB skills are not good, I can often work with code from this group,but I am stuck on this. the code below will save a copy of a worksheet to the H Drive with a file name with sheet name (paddock), cell reference and date time .xls My problem is to check if H drive exists, then if it does not exist, to prompt to SAVE As for user input to select another file on C drive with the given file name. appreciate any help on this. here is the code. Sub SaveASPaddock() 'This macro will SAVE AS to H:\ folder, which must exist before macro runs ActiveSheet.Unprotect Sheets("Paddock").Select Range("A3").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy Workbooks.Add Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues Selection.PasteSpecial Paste:=xlPasteFormats Range("A1").Select Application.CutCopyMode = False ActiveWorkbook.SaveAsApplication.GetSaveAsFilename "H:\" & "PaddockFee_" & Range("D1").Value & "_" & Format(Now, "yyyymmddhhmm") & ".xls" ActiveWorkbook.Close SaveChanges:=False Sheets("Paddock").Select Range("A3").Select ActiveSheet.Protect UserInterfaceOnly:=True End Sub Thanks in advance Cheers Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
check if drive exists - if not prompt to SAVE AS box
thanks Jim
Appreciate the asisstance here. Trying out this code now. Cheers Paul On Dec 18, 11:39*am, "Jim Cone" wrote: You can check for the existence of a drive using the FileSystemObject... '-- Function DriveStatus(drv As String) As Boolean * *Dim fso As Object * *Set fso = CreateObject("Scripting.FileSystemObject") * *DriveStatus = fso.DriveExists(drv) * *Set fso = Nothing End Function '-- So... If DriveStatus("H") = True Then * *'save file Else * 'chose another drive End If -- Jim Cone Portland, Oregon *USA "Paul" wrote in message Hi All, I would really appreciate some help on this as my VB skills are not good, I can often work with code from this group,but I am stuck on this. the code below will save a copy of a worksheet to the H Drive with a file name with sheet name (paddock), cell reference and date time .xls My problem is to check if H drive exists, then if it does not exist, to prompt to SAVE As for user input to select another file on C drive with the given file name. appreciate any help on this. here is the code. Sub SaveASPaddock() 'This macro will SAVE AS to H:\ *folder, which must exist before macro runs * * ActiveSheet.Unprotect * * Sheets("Paddock").Select * * Range("A3").Select * * Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select * * Selection.Copy * * Workbooks.Add * * Range("A1").Select * * Selection.PasteSpecial Paste:=xlPasteValues * * Selection.PasteSpecial Paste:=xlPasteFormats * * Range("A1").Select * * Application.CutCopyMode = False * * ActiveWorkbook.SaveAsApplication.GetSaveAsFilename "H:\" & "PaddockFee_" & Range("D1").Value & "_" & Format(Now, "yyyymmddhhmm") & ".xls" * * ActiveWorkbook.Close SaveChanges:=False * * Sheets("Paddock").Select * * Range("A3").Select * * ActiveSheet.Protect UserInterfaceOnly:=True End Sub Thanks in advance Cheers Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save workbook To any USB Drive no matter what drive letter | Excel Programming | |||
Can I save to hard drive AND my flash drive at the same time? | Excel Discussion (Misc queries) | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
Save to hard drive and backup to thumb drive. | Excel Programming | |||
Save file to Network Drive and prompt for name | Excel Programming |