Saving temporary text file to particular location with the name of current Date and Time

Hi,
I am extracting my company daily production information from oracle application in text mode and i am copying the information to my worksheet using simple recorded macro at least minimum of 10 times a day to monitor the production.

The issue now is if i wanted to look back the production details by 9 am i don't have the data for since i have already updated with the latest reports.

So i just wanted to check the possibilities of saving the text report (Temporary internet file or from the clipboard since the data is already copied to clip board) in the location of "\\ZSI24261\PMS" (Shared Folder) with the name of current date and time after completing the below procedure. (Sorry I don't know where to post the code so i have given it here please advise me i will do it in future)

Sub Import_PMSData()
'
' Import_PMSData Macro
' Macro to import data's of Planning Management Report.
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    Range("L6").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
    Range("L6").Select
End Sub

I can save the text file when i am uploading the latest data's from oracle but the issue is this file has been shared within our department and some of staffs are updating this data from their side as well so they are not prompt on doing this action (Even i am forgetting to do this some times).

Expecting your support on this issue which will help us a lot.


Regards
Muralidaran.

Could someone help me on this issue please.....

---------- Post added 08-04-2012 at 12:07 AM ---------- Previous post was 08-03-2012 at 10:51 PM ----------

Hi Cutter
Could you please tell me how to do code tag it will help me in future....

muralidaran,

Add this line to the end of your macro:

ActiveSheet.SaveAs "\\ZSI24261\PMS\" & Format(Now, "dd-mmm-yyyy hh_mm_ss AM/PM") & ".txt", xlUnicodeText

As for how to use code tags, you just need to select the code and press the # button. Or you can manually type in the code tags with:

[code]
Your Code Here
[/code]

Thanks for your support Tigeravatar, but i had a problem here when i executed the macro it has changed my sheet name in the workbook as well which i don't wanted to how to restrict it, please advise.

And also i don't wanted to take the entire sheet since it is having some formulas as well, Please suggest me to capy a particular range say like L6:BB500 which is purely having the raw data which i have copied from the original text file.

And off course thanks for giving tips about the code tag.

muralidaran,

Give this version a try:

Sub Import_PMSData()
'
' Import_PMSData Macro
' Macro to import data's of Planning Management Report.
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Range("L6").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
    
    Application.ScreenUpdating = False
    Sheets.Add.Move
    With ActiveWorkbook.Sheets(1)
        ws.Range("L6").CurrentRegion.Copy
        .Range("A1").PasteSpecial xlPasteValues
        .SaveAs Environ("UserProfile") & "\Desktop\" & Format(Now, "dd-mmm-yyyy hh_mm_ss AM/PM") & ".txt", xlUnicodeText
        .Parent.Close False
    End With
    Application.ScreenUpdating = True
    
End Sub

muralidaran,

See my last post. It should perform as you've described. If necessary, you replace this line:

ws.Range("L6").CurrentRegion.Copy

With this instead:

ws.Range("L6:BB500").Copy

Bundle of thanks, Thats what i did and it works fine.

Sub Import_PMSData()
'
' Import_PMSData Macro
' Macro to import data's of Planning Management Report.
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Range("L6").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
    
    Application.ScreenUpdating = False
    Sheets.Add.Move
    With ActiveWorkbook.Sheets(1)
        ws.Range("L6:R24").Copy
        .Range("A1").PasteSpecial xlPasteValues
        .SaveAs Environ("UserProfile") & "\Desktop\" & Format(Now, "dd-mmm-yyyy hh_mm_ss AM/PM") & ".txt", xlUnicodeText
        .Parent.Close False
    End With
    Application.ScreenUpdating = True
    
End Sub

You're very welcome

Hi
I have a issue again, i have changed the code slightly according to my staff's requirements and due to that the data has been not copied to the text file but it has successfully created the text file, please find the below code which i have used.

Sub Import_PMSData()
'
' Import_PMSData Macro
' Macro to import data's of Planning Management Report.
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Sheets("PMS All").Select 'If remove this condition it is copiying the data to the text file.
    Range("P3:DK903").Select
    Selection.ClearContents
    Range("P3").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
    Range("P3").Select
    Application.ScreenUpdating = False
    Sheets.Add.Move
    With ActiveWorkbook.Sheets(1)
        ws.Range("P3:DK903").Copy
        .Range("A1").PasteSpecial xlPasteValues
        .SaveAs Environ("UserProfile") & "\Desktop\" & Format(Now, "dd-mmm-yyyy hh_mm_ss AM/PM") & ".txt", xlUnicodeText
        .Parent.Close False
    End With
    Application.ScreenUpdating = True
    Sheets("Home").Select
    Range("J5").Select
    Selection.Copy
    Range("J23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("K5").Select
    
End Sub

Please help me in this issue and please be noted that the workbook which we are using this code is shared.

Hi
Could someone help me on this issue please....

muralidaran,

Give this a try:

Sub Import_PMSData()
'
' Import_PMSData Macro
' Macro to import data's of Planning Management Report.
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    
    Dim ws As Worksheet
    Set ws = Sheets("PMS All")
    
    With ws
        .Select
        .Range("P3:DK903").ClearContents
        .Range("P3").Select
        .PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False
    End With
    
    Application.ScreenUpdating = False
    Sheets.Add.Move
    With ActiveWorkbook.Sheets(1)
        ws.Range("P3:DK903").Copy
        .Range("A1").PasteSpecial xlPasteValues
        .SaveAs Environ("UserProfile") & "\Desktop\" & Format(Now, "dd-mmm-yyyy hh_mm_ss AM/PM") & ".txt", xlUnicodeText
        .Parent.Close False
    End With
    Application.ScreenUpdating = True
    
    With Sheets("Home")
        .Select
        .Range("J5").Copy
        .Range("J23").PasteSpecial xlPasteValues
        .Range("K5").Select
    End With
    Application.CutCopyMode = False
    
End Sub