same workbook name in one folder

[freezefiz]

hey guyz..

Pls advise me on this.

Is it possible to have saved the same workbook name and that to have a prompt asking whether you want to replace or update the workbook??

eg. If update, it'll scan through the existed workbook & compare with the other workbook which you want to save, & add rows according or sum up the total for one of the cols.

Pls pls let me know if this can be done..

 

[freezefiz]

hopefully this will help a little...

And this is the entire codings :

If IsDate(Range("B1")) = False Then 'simple msg box to check if date,cell(b1) is empty
  MsgBox "Invalid Date!", vbExclamation, "No Date" 'no update until date,cell(b1) is filled
Else
        Dim intRow As Integer
        Dim rProd As Range
        intRow = 2
        Range("c1").Value = "=COUNTA(db!D:D)"
        nDb = Range("c1").Value
        
        Do While Worksheets("db").Cells(intRow, 2).Value <> ""
            If CStr(Worksheets("db").Cells(intRow, 1).Value) = Sheets("GrandTotal").Range("b1").Value Then 'if date value match...
                
                
                proID = Worksheets("db").Cells(intRow, 6).Value
                pro = Worksheets("db").Cells(intRow, 7).Value
                uom = Worksheets("db").Cells(intRow, 10).Value
                qty = Worksheets("db").Cells(intRow, 9).Value
             
                
                qty = "=SUMPRODUCT(--(db!R2C1:R[" & nDb & "]C1=R1C2),--(db!R2C6:R[" & nDb & "]C6=RC1),--(db!R2C7:R[" & nDb & "]C7=RC2),--(db!R2C10:R[" & nDb & "]C10=RC3),db!R2C9:R[" & nDb & "]C9)"
                                          'Date'                             'prodID'                         'prod'                            'uom'                          'qty'
              
                Range("b2").Formula = "=IF(ISNUMBER(B1),TEXT(WEEKDAY(B1),""dddd""),""Day?"")"
                Set rProd = Sheets("GrandTotal").Range("a" & Columns.Count).End(xlUp)
                rProd.Offset(1, 0).Resize(, 2) = Array(proID, pro & " (" & uom & ")")
                             
                Set rUOM = Sheets("GrandTotal").Range("c" & Columns.Count).End(xlUp)
                rUOM.Offset(1, 0).Resize(, 2) = Array(uom, qty)
               
  
            End If 'end of if date value match...
        
            intRow = intRow + 1
        Loop
 
               Dim lngTMP As Long, iRows As Long
    On Error GoTo Fin
    Application.ScreenUpdating = False
    iRows = Cells(Cells.Rows.Count, 2).End(xlUp).Row
    For lngTMP = iRows To 6 Step -1
        If WorksheetFunction.CountIf(Columns(2), Cells(lngTMP, 2)) > 1 Then
            Rows(lngTMP).Delete
        End If
    Next lngTMP
Fin:
    Application.ScreenUpdating = True
 
  
Sheets("GrandTotal").Columns("B:B").Replace What:=" (*)", Replacement:="", LookAt:=xlPart

End If 'end of if date empty code
    With Sheets("GrandTotal").Range("b" & Rows.Count).End(xlUp).Offset(, -1)
   .Offset(3).Resize(2).Value = [{"handover by:";"received by:"}]
End With
 Range("c1").Value = ""

This is how its saved.

Dim NewName As String
NewName = Format$(ThisWorkbook.Sheets("GrandTotal").Range("b1").Value, "dd_mmm_yyyy")
   Dim box1 As Integer
    box1 = MsgBox("Create workbook" & NewName & "?", 1 + vbInformation, "Create production?") 'msg box: once date,cell(b1) is filled, confirm again to update
    If box1 = 1 Then
If Dir(ThisWorkbook.Path & "\" & "Production" & NewName) <> "" Then
Else
Dim wbkName As String
Dim Sh As Worksheet
Set Sh = Worksheets("GrandTotal")
Application.ScreenUpdating = False
Set ShNew = Workbooks.Add
         
         With ShNew
                
    Dim name As String
    name = Format$(ThisWorkbook.Sheets("GrandTotal").Range("B1").Value, "dd_mmm_yyyy")
               
               wbkName = name
Sh.Cells.Copy
ShNew.Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteFormats
ShNew.Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
Dim num As Integer
num = num + 1
ShNew.SaveAs ThisWorkbook.Path & "\" & "PRODUCTION" & "\" & name & "_(" & num & ")"
ShNew.Close True
        End With
    End If
End If

i appreciate you guys trying to understand my problem.

 

[Guanjin Peter]

I think they mean is, you require more details:

eg. If update, it'll scan through the existed workbook & compare with the other workbook which you want to save, & add rows according or sum up the total for one of the cols.

existed workbook? > You mean the current workbook?(active workbook?)
other workbook which you want to save? > do they already exist, or it is created?
add rows according or sum up the total for one of the cols > adding empty rows? sum up the totals at where? which column?

 

[jindon]

freezefiz,

Posting code doesn't help us understand.
How do you want to evaluate the difference ?
File a you have

TDG-**002 | Tuna Cheese Pizza Bar | KG | 30

then if you have

TDG-**002 | Tuna Cheese Pizza Bar | KG | 40

in file b

Is the line different? and how do you want it to "Update"?

 

[freezefiz]

so sorry you guyz for being so complicated..

just that so stressed up about this.

ok jindon, from the example that you've posted,
i would like the colA to colC to remain as one row.(but to update the colD.

So the desire output would be:
TDG-**002 | Tuna Cheese Pizza Bar | KG | 70

Well, at then end of it, there'll only be one workbook being named "18JUNE08.xls".

 

[jindon]

Well, at then end of it, there'll only be one workbook being named "18JUNE08.xls".

But you are saving

ThisWorkbook.Path & "\" & "PRODUCTION" & "\" & name & "_(" & num & ")"

Which is correct ?

 

[freezefiz]

sorry for the confusion..

initially i thought i'd want to create new worbooks for each users.

But i was hoping i need not do that anymore and just create one workbook.

Lets' say :
user A saved it as "18JUNE08.xls"(oh and this date is taken from cell B2)

user B then tried to save the same date again.
Usual case is that i'd be asked to overwrite but i was hoping to update it instead.

so eventually the workbook that was saved by userA will be use to compare with other users.

hmm... i hope i'm making sense here..

 

[jindon]

OK then file name is now fine
So when you saveas "18JUNE08.xls"
1) check if the file exists in the same folder.
2) if yes, combine the data.
3) overwrite with new file

Is this what you wanted ?

 

[freezefiz]

yes, jindon.

no. 1-3 are the things i'd like to achieve.

and then the file will be saved again as "18JUNE08.xls"

hmm.. just wondering, are these even possible to achieve or far from possible?

Thanks.

 

[freezefiz]

Just to claridy my doubts..

Excel has this default prompt :

A file named 'X:\JUNE\PRODUCTION\18JUNE08.xls' already exists in this location. Do you want to replace it?

If the user selects 'NO', how do we add another prompt for the user to save it as another name?

Pls Advise.

 

[jindon]

freezefiz<SCRIPT type=text/javascript> vbmenu_register("postmenu_1600818", true); </SCRIPT>

It will be easily done, if you explain what you want to do from the first place.
I need to write the code from the scratch, you know what I mean ?

1) Edit particulat worksheet
2) copy the worksheet to generate a new workbook
3) name the new workbook as

NewName = Format$(ThisWorkbook.Sheets("GrandTotal").Range("b1").Value, "dd_mmm_yyyy")

4) if same file already in the folder, combine them

I need a clear explanation of 1)
1) what is the name of the worksheet
2) worksheet layouts with before/after with clear explanations.