Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to save Excel file
I have a macro that saves an Excel file with specific name and path (see
below). However, sometimes I forget the fill out the information needed like time, date or hours and the file is been saved without this information. How can I get a message box to tell me that one or all the range needed have not been filled? Thanks in advance. Maperalia Sub SaveExcelFile() Dim Tract As String Dim WO As String Dim Supplier As String Dim Dates As String Dim Hours As String Dim sFilename As String WO = Worksheets("Gradation Form").Range("G2") Tract = Worksheets("Gradation Form").Range("G3") Supplier = Worksheets("Gradation Form").Range("C6") Dates = Worksheets("Gradation Form").Range("G4") Hours = Worksheets("Gradation Form").Range("G5") Progname = "C:\Mario\VB\Excel\" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" & Hours & ".xls" ActiveWorkbook.SaveCopyAs Progname End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to save Excel file
How about adding something like:
Dim myRngToComplete as range with worksheets("gradation form") set myrngtocomplete = .range("G2:G5,C6") end with if myrngtocomplete.cells.count < application.counta(myrngtocomplete) then msgbox "oh, oh! Cannot save!" exit sub end if maperalia wrote: I have a macro that saves an Excel file with specific name and path (see below). However, sometimes I forget the fill out the information needed like time, date or hours and the file is been saved without this information. How can I get a message box to tell me that one or all the range needed have not been filled? Thanks in advance. Maperalia Sub SaveExcelFile() Dim Tract As String Dim WO As String Dim Supplier As String Dim Dates As String Dim Hours As String Dim sFilename As String WO = Worksheets("Gradation Form").Range("G2") Tract = Worksheets("Gradation Form").Range("G3") Supplier = Worksheets("Gradation Form").Range("C6") Dates = Worksheets("Gradation Form").Range("G4") Hours = Worksheets("Gradation Form").Range("G5") Progname = "C:\Mario\VB\Excel\" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" & Hours & ".xls" ActiveWorkbook.SaveCopyAs Progname End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to save Excel file
Dave;
Thank you very much it is working wonderful!!!!!! "Dave Peterson" wrote: How about adding something like: Dim myRngToComplete as range with worksheets("gradation form") set myrngtocomplete = .range("G2:G5,C6") end with if myrngtocomplete.cells.count < application.counta(myrngtocomplete) then msgbox "oh, oh! Cannot save!" exit sub end if maperalia wrote: I have a macro that saves an Excel file with specific name and path (see below). However, sometimes I forget the fill out the information needed like time, date or hours and the file is been saved without this information. How can I get a message box to tell me that one or all the range needed have not been filled? Thanks in advance. Maperalia Sub SaveExcelFile() Dim Tract As String Dim WO As String Dim Supplier As String Dim Dates As String Dim Hours As String Dim sFilename As String WO = Worksheets("Gradation Form").Range("G2") Tract = Worksheets("Gradation Form").Range("G3") Supplier = Worksheets("Gradation Form").Range("C6") Dates = Worksheets("Gradation Form").Range("G4") Hours = Worksheets("Gradation Form").Range("G5") Progname = "C:\Mario\VB\Excel\" & WO & "_" & Tract & "_" & Supplier & "_" & Dates & "_" & Hours & ".xls" ActiveWorkbook.SaveCopyAs Progname End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save Macro in excel to be used on schedule file | Excel Discussion (Misc queries) | |||
internet connection via excel macro at timed intervels & save data to excel file. | Excel Worksheet Functions | |||
ASP: Open Excel File with Macro, Allow Macro to run, and then save | Excel Programming | |||
Macro to save excel file | Excel Programming | |||
Automate open file, update links, run macro, close and save file | Excel Programming |