1

I have been working in a code to paste the data from the vba file to another file. The name of the file which i paste the data is always "endofdaydividends" and i need to save this file everyday in the same place and replace the old one. I would like to save in my desktop. 

Sub move_to_csvfile()
Dim x As Workbook
Dim y As Workbook
Dim strPath As String
Dim fileName As String
Dim dd As String

Set x = ThisWorkbook
Set y = Workbooks.Open("T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv")
    Application.DisplayAlerts = False
    x.Sheets("Code").Range("A:F").EntireColumn.Copy
    Application.DisplayAlerts = True
    Application.DisplayAlerts = False
    y.Sheets("endofdaydividends").Range("A1").PasteSpecial
    Application.DisplayAlerts = True
    x.Close False
    ActiveWorkbook.Save
End Sub

With this code, i close the macro file and leave opened only the "endofdaydividends" file. Does anyone know how to add a command to save it on my folder? I need to save in this address T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv

Community
  • 1
  • 1
user7004
  • 185
  • 2
  • 3
  • 11
  • I am a little bit confused, aren't you already saving it? maybe you can change `ActiveWorkbook.Save` to `y.Save` – Dubison Apr 29 '15 at 08:10
  • I wrote the activeWorkbook.Save trying to save but it didnt work. I have also tried your suggestion now but it is still not working – user7004 Apr 29 '15 at 08:16
  • seems a little strange that you're closing thisworkbook and then trying to save 'y'. try swapping the lines around, so x.close is the very last thing you do? – tea_pea Apr 29 '15 at 08:29
  • thank you, i deleted this line(its not importat to close x) and tried the following to save.. but it didnt work y.Sheets("endofdaydividends").save fileName:="T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv", FileFormat:=xlCSV, CreateBackup:=False – user7004 Apr 29 '15 at 08:35
  • try `y.SaveAs Filename:="T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv", FileFormat:=xlCSV, CreateBackup:=False`we might have to do something further to ensure it overwrites the current file completely (maybe delete current file first or turn off alerts). – tea_pea Apr 29 '15 at 09:10
  • Hint: Look at http://stackoverflow.com/a/5798392/380384 and use a `FileSystemObject` for fast file operations. – John Alexiou Apr 29 '15 at 15:17

1 Answers1

0

Can you please try following?

Code to delete your previous file, need to add to beginning of your function before pasting the new value:

SetAttr "T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv", vbNormal
Kill "T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv"

Code to save the new file:

ActiveWorkbook.SaveAs Filename:="T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv", FileFormat:=xlCSV, CreateBackup:=False

UPDATED CODE: Your Code should look like this. What we do here, we first delete existing CSV file then, create a new file and rename the active sheet to what you desire then paste required values and save the new created workbook as new CSV.

Sub move_to_csvfile()
Dim x As Workbook
Dim y As Workbook
Dim strPath As String
Dim fileName As String
Dim dd As String

Set x = ThisWorkbook
    On Error Resume Next
    SetAttr "T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv", vbNormal
    Kill "T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv"
    On Error GoTo 0
Set y = Workbooks.Add
    y.ActiveSheet.Name = "endofdaydividends"
    Application.DisplayAlerts = False
    x.Sheets("Code").Range("A:F").EntireColumn.Copy
    Application.DisplayAlerts = True
    Application.DisplayAlerts = False
    y.Sheets("endofdaydividends").Range("A1").PasteSpecial
    Application.DisplayAlerts = True
    y.SaveAs fileName:="T:\CTG\EDM\Current EDM LDN\MacroEODDividend\endofdaydividends.csv", FileFormat:=xlCSV, CreateBackup:=False
    x.Close False
End Sub
Dubison
  • 750
  • 5
  • 12
  • it does not work. The code runs, but when i check the folder it still appears the file from yesterday – user7004 Apr 29 '15 at 08:26
  • I have updated the answer with small code to delete previous file. Lets give it a try. – Dubison Apr 29 '15 at 10:04
  • is this going to delete the macro file or only close? because i will need it after – user7004 Apr 29 '15 at 10:05
  • i can not delete my first file, because it has the macro and i will need it after – user7004 Apr 29 '15 at 10:14
  • this will delete the CSV file. Is your macro in the CSV file or in the source file? – Dubison Apr 29 '15 at 10:52
  • yes, it deleted the CSV file. but the problem is that when i run the macro, it does not recognize the "endofdaydividends" because it was already deleted.. do you know how to insert a code to create this csv file? Then the macro will recognize and save it – user7004 Apr 29 '15 at 10:55
  • I have updated the code, now I update all the code you need and put some explanations. I do not have an excel right now that is why I could not test it. If you let me know about the result I will see what I can do. – Dubison Apr 29 '15 at 12:09
  • Thanks for your patience. The code runs, but still does not save the y file in the specified folder – user7004 Apr 29 '15 at 12:58
  • I have made some final tuning in the code, it had some problems. This may work fine now. I finally had the chance to try it out and working. If it is working you can accept the answer :) – Dubison Apr 29 '15 at 13:16