• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I Save .xlsx file where Saved File Name will generate Cell value and current Date using VBA command button ?

Porag

New Member
Hi Expert,

Please find the attached excel macro enabled file where I've added a Command Button for Saving file Name with Cell value and current date but getting an error message, "Runtime Error 6 overflow" , How to fix it ? another requirement after saving the .xlsx file to Selected path data should be 1. Paste Special with value 2. Saved Sheet Name should be Cell value. Need yours cordial help and advise on this with Sample example as this is my very urgent Task.

Thanks
Porag
 

Attachments

  • PT_Data.xlsm
    110 KB · Views: 2
As per the first issue just change the declaration of the variable invno to String instead of Long.
Sorry but I didn't understand the second request.
 
As per the first issue just change the declaration of the variable invno to String instead of Long.
Sorry but I didn't understand the second request.

Hi Rollis,

Thanks for your advise about Data type Long to String and need more solutions which requirements are pointed below

A. When .xlsx file save in Selected Path then Excel file data should be show on Paste Special Value mode not formula mode but my excel file saved on formula Mode on cell value, you can check my file after saved.

B. Saved excel file sheet Name should be excel cell value, Like : Range ("B2") value is : "4900122249" then Saved Excel file Sheet Name should be "4900122249". I've attached the Macro Enabled Excel sheet "PT_Data" where sheet Name "ERP_Data" and when saved the file this Sheet Name Should be "4900122249" which value from Range ("B2") instead of "ERP_Data"

Could you send me an example Macro Enabled Excel sheet with my required solutions ? This urgent Task for me dear, please help on this

 

Attachments

  • PT_Data.xlsm
    110.1 KB · Views: 5
Please remember that queries are answered by volunteers in their own free time. So, no rush please, for urgent tasks you can request for a fee in specific sites.
Added some code:
Code:
Option Explicit
Private Sub CommandButton1_Click()
    Dim path   As String
    Dim fname  As String
    Dim invno  As String
    path = "D:\Urgent_OfficeWork_Porag\PEERLESS\"
    invno = Range("B2")
    fname = invno & " - " & Range("A2")
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False            '<- added
    Sheet1.Copy
    '------------------------added---------------------------------
    With ActiveSheet
        .Range("A1").Select
        .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
    '--------------------------------------------------------------
    ActiveSheet.Shapes("CommandButton1").Delete
    With ActiveWorkbook
        .SaveAs Filename:=path & fname & Format(Date, "DD-MM-YY"), FileFormat:=51
        .Close
    End With
    MsgBox "ERP Data" & " " & path & " " & vbCrLf & fname & " " & "Saved Successfully", vbInformation, "Data Save"
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True             '<- added
End Sub
 
Please remember that queries are answered by volunteers in their own free time. So, no rush please, for urgent tasks you can request for a fee in specific sites.
Added some code:
Code:
Option Explicit
Private Sub CommandButton1_Click()
    Dim path   As String
    Dim fname  As String
    Dim invno  As String
    path = "D:\Urgent_OfficeWork_Porag\PEERLESS\"
    invno = Range("B2")
    fname = invno & " - " & Range("A2")
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False            '<- added
    Sheet1.Copy
    '------------------------added---------------------------------
    With ActiveSheet
        .Range("A1").Select
        .Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
        Selection.PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End With
    '--------------------------------------------------------------
    ActiveSheet.Shapes("CommandButton1").Delete
    With ActiveWorkbook
        .SaveAs Filename:=path & fname & Format(Date, "DD-MM-YY"), FileFormat:=51
        .Close
    End With
    MsgBox "ERP Data" & " " & path & " " & vbCrLf & fname & " " & "Saved Successfully", vbInformation, "Data Save"
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True             '<- added
End Sub
Hi rollis13,
Thanks a lot for your great Help with faster response. Thanks again dear.
 
Thanks for the feedback, glad having been of some help.

Hi Rolls 13,

One issue still having problem after importing Text data into excel using vba command how to keep the excel formatting cell? Otherwise after importing the text data into excel automatically changed the excel cell format that is why Date format changing and another column 24 digits numeric value also changed like scientific symbol. How to fix it? And other issue, I need data only save based on excel row value no need entire sheet saved ( Like as example Range A1 to H20 data is available in active sheet then only save those range, if data increase or decrease the row then only those range value will save) Yours help and advise highly expecting.
 
Not sure what you are talking about. This macro doesn't import data, it copies data and saves a new file. In your example there are no dates that can change format and neither long number that change to scientific notation.
If you have different questions that don't concern "Save .xlsx file" subject of this thread I suggest you close this one and start a new thread with appropriate title and take note "one thread, one question".
 
Not sure what you are talking about. This macro doesn't import data, it copies data and saves a new file. In your example there are no dates that can change format and neither long number that change to scientific notation.
If you have different questions that don't concern "Save .xlsx file" subject of this thread I suggest you close this one and start a new thread with appropriate title and take note "one thread, one question".

Hi Rollis 13,

Noted with thanks, I'll follow the same.
 
Back
Top