Help with Code Please

[slimimi]

Hi there.

I have this code so far using Macro Recorder but i wanted to change a few parts which i am not too sure how to do:
Its basically supposed to look in specified folder for all files, move them to Main Workbook & rename them slightly

Sub getcsv()


Workbooks.Open Filename:= _
"C:\Documents and Settings\Slim\Desktop\Positions\Charts\AUDCADm1440.csv"

Sheets("AUDCADm1440").Select

Sheets("AUDCADm1440").Move After:=Workbooks("Position Risk Calc v9.8.xls"). _
Sheets(23)


End Sub

1. Slim
Would like to change this to Environment. I tried *& Environment("USERNAME") &* but this did not work for me.

2.AUDCADm1440.csv
Would like to change this so that code looks for ALL .csv files in this folder instead of 1 specific file

3.Sheets(23)
Would like to change this to look for whatever the last sheet in the main Workbook is (rather than a specific sheet number)

4.Position Risk Calc v9.8.xls"
Would like this to be whatever the main workbook name is from where this macro is fired from. (as i go along - i develop and change the name of the file : guess i am just trying to avoid having to change this code each time if thats possible).

Lastly
I would like the sheet names, for all these *moved* sheets to be renamed by replacing the "m1440" with ""

Phew....

I cant seem to get macro recorder to dish out the results i need when i try to achieve each of the following above

Thanks a big heap in advance....

 

[slimimi]

Hi Alexander - i went with this code in the end which seemed to work perfectly.

Sub refreshCSV()

Dim my_csv

Application.ScreenUpdating = False

For Each my_csv In Array("audcadm1440", "audjpym1440", "audnzdm1440", "audusdm1440", "chfjpym1440", "euraudm1440", _
"eurcadm1440", "eurchfm1440", "eurgbpm1440", "eurjpym1440", "eurusdm1440", "gbpchfm1440", _
"gbpjpym1440", "gbpusdm1440", "nzdjpym1440", "nzdusdm1440", "usdcadm1440", "usdchfm1440", "usdjpym1440")

Application.DisplayAlerts = False

Sheets(my_csv).Delete

ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\Positions\Charts"
Workbooks.Open Filename:= _
"C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\Positions\Charts\" & my_csv & ".csv", _
Origin:=xlWindows
Sheets(my_csv).Select
Sheets(my_csv).Move Before:=ThisWorkbook.Worksheets(Sheets.Count)
Sheets(my_csv).Visible = False

Next my_csv

Application.ScreenUpdating = True

End Sub

Thanks for all your help on it though....

 

[xenou]

Great, that looks good.
AB

 

[xenou]

So if this works too the main thing is that we will have an error handler so if something goes wrong and you crash it will exit gracefully and turn screenupdating back on...

Sub refreshCSV()
Dim my_csv
Dim intCount

On Error Goto Handler:
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Array of sheets that will be deleted and re-created from csv files
For Each my_csv In Array("audcadm1440", "audjpym1440", "audnzdm1440", "audusdm1440", "chfjpym1440", "euraudm1440", _
"eurcadm1440", "eurchfm1440", "eurgbpm1440", "eurjpym1440", "eurusdm1440", "gbpchfm1440", _
"gbpjpym1440", "gbpusdm1440", "nzdjpym1440", "nzdusdm1440", "usdcadm1440", "usdchfm1440", "usdjpym1440")

    'Delete Sheet in mainworkbook
    Sheets(my_csv).Delete

    'This line may not be needed - I don't think you need to change directories to open the file
    ChDir "C:\Documents and Settings\" & Environ("USERNAME") & "\Desktop\Positions\Charts"
    
    'Open csv file and move the sheet into the main workbook
    Workbooks.Open Filename:="C:\Documents and Settings\" _
        & Environ("USERNAME") & "\Desktop\Positions\Charts\" _
        & my_csv & ".csv", Origin:=xlWindows
    
    Sheets(my_csv).Select
    Sheets(my_csv).Move Before:=ThisWorkbook.Worksheets(Sheets.Count)
    
    'Hide sheet after moving it to main workbook
    Sheets(my_csv).Visible = False

    'Counter
    intCount = intCount + 1

Next my_csv

'Report results of successful move and exit sub
Application.ScreenUpdating = True
MsgBox intCount & " sheets were successfully moved."
Exit Sub

'Handle errors - turn ScreenUpdating back on.
Handler:
Application.ScreenUpdating = True
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf & intCount & " Sheets were moved before execution was interrupted."
End Sub

 

[slimimi]

Hi there Alexander - THANK YOU THANK YOU - this works beautifully now... ))))

I just want to improve the error handling if possible please.
Will try my best to explain:

At the moment - i am seeing the result from the message box once macro finishes.

MsgBox intCount & " Currency Pairs have been Updated", vbInformation

This is great.

Now if delete one of the files (to test) then run code - it goes to error handler.
Now if i put back the file into the correct folder and try to run code again - it keeps going to error handler. Does this mean i need to restart my workbook each time this happens? Is there any way around this?

Perhaps if its possible to, instead of going to error handler, do the following: (assuming i remove the AUDCADm1400 file from the Charts folder for this example):

1. Still run macro
2. Report Results as Count - so - msgbox saying that "x no. of charts were moved"
3. Followed by a WARNING msgbox to report (in this instance) something like :

AUDCADm1440 was not found in your Charts Folder.
You will not be able to view Historical Data for this Currency Pair.
Please go to MetaTrader4, Save this Chart and place in your Charts Folder.

Would this be possible?

Ok on to the other thing.

Instead of reporting the following :

MsgBox intCount & " Currency Pairs have been Updated", vbInformation

Would it be possible to expand this to show a List of All the Files that were moved, and, next to each chart, show the Date last updated.

By 'Date last Updated' i mean that, for each .csv file, to find the last row and return the value from Column A (which happens to be a date column anyway so i think this might work nicely).

Is that possible Alexander?

Thanks a million in advance....