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....

 

[xenou]

1)
How many files are there...
(curious)

2)
Do they all have a sheet by this name: Sheets("AUDCADm1440").Select
(maybe some deduction here would tell me no...I'm guessing the sheet names are the same as the file names...)

3)
Do you get an error moving the only sheet in the csv file? I thought you can't leave a workbook with "no sheets" in it? Do you want these old files deleted or saved to a backup folder for later removal?

This is too much for the macro recorder, as you have discovered.



Edit: In quick answer to one of your questions, ENVIRON("enviroment variable name") is the syntax you are looking for.

AB

 

[xenou]

I don't really like trashing your files but I've set this up to delete the csv's if they successfully move to the main workbook.

I'd prefer that you provide a folder that we can move these files to for disposal later (we'd add the date/time to the filenames too).

Please backup everything before you try this. My ENVIRON Variable returns the path to my documents and settings profile, so check in your immediate window what the ENVIRON() function is returning and build your path to the folder as needed....

Sub getcsv()
Dim oFileSystem As Object
Dim oFolder As Folder
Dim oFile As File
Dim strTemp As String
Dim wbMain As Workbook
Dim blnNoErrors As Boolean

'Reference to workbook that is active when code is run (main workbook)
Set wbMain = ActiveWorkbook

Set oFileSystem = CreateObject("Scripting.FileSystemObject")
strTemp = Environ("USERPROFILE") & "\Desktop\Positions\Charts\"

If oFileSystem.FolderExists(strTemp) Then
    Set oFolder = oFileSystem.GetFolder(strTemp)
    For Each oFile In oFolder
        strTemp = oFile.Path
        Call AddToMainWorkbook(wbMain, strTemp, blnNoErrors)
        If blnNoErrors Then oFile.Delete
    Next oFile
Else
    MsgBox "Path is invalid: " & vbCrLf & vbCrLf & strTemp, vbInformation
    
End If
End Sub
'--------------------
Sub AddToMainWorkbook(ByRef wbMain As Workbook, ByRef strPath As String, ByRef blnNoErrors As Boolean)
Dim wb As Workbook

On Error GoTo Handler:
blnNoErrors = True
Set wb = Workbooks.Open(strPath)
wb.Sheets(1).Copy After:=wbMain.Sheets(wbMain.Sheets.Count)

Exit Sub
Handler:
blnNoErrors = False
End Sub

 

[cornflakegirl]

1. I don't really understand what you mean by this.
2. You'll need to loop through all the files in the folder. I believe you can use Dir() to do this.
3. Try Sheets(Sheets.Count)
4. Try ThisWorkbook
5. Try the Mid property

 

[slimimi]

Hi there Alexander.

There are 19 files altogether.

All the sheetnames will be different but they will all end in m1440.
I have provided the full list below of all the files that will be in this folder.

I do not get an error when moving the file.
Its better this way because i do not have to close the .csv file after.

These old files can stay as they are - they dont need to be deleted. They need to stay sitting in the Charts folder.

Thanks in advance.

 

[xenou]

Hmmm, the plot thickens then. How will we know what files should have the worksheets copied/moved? Do these same 19 files always stay there, the same files? I guess I was thinking they'd be new files all the time that need to be copied. So if we run the routine, we always process all the files in the folder, right? No exceptions?

In my previous code, change the code that says .Delete - just get rid of that line! Maybe that will do the trick for you. It just works on all the files in the folder regardless of their names. My code doesn't move the sheets, it copies them, but then it also closes the files...so no difference in the end. (
I guess if you move a tab from a workbook with only one tab, you de facto close the workbook - I didn't know that).

AB


Edit:
I mean, Change this:

If blnNoErrors Then oFile.Delete

to (maybe):

If Not blnNoErrors Then msgbox "An error occurred moving sheet " & oFile.Name

And also Change:

On Error GoTo Handler:
blnNoErrors = True
Set wb = Workbooks.Open(strPath)
wb.Sheets(1).Copy After:=wbMain.Sheets(wbMain.Sheets.Count)

To:

On Error GoTo Handler:
blnNoErrors = True
Set wb = Workbooks.Open(strPath)
wb.Sheets(1).Move After:=wbMain.Sheets(wbMain.Sheets.Count)

(using move as that seems to be working great for you!)

 

[slimimi]

Hi Alexander - ok - i think i didnt make the situation clear from the start (my apologies for that). This is the whole picture:

There is a subfolder in the <positions> folder.
This subfolder is called <charts>.

Now in this <charts> folder - there will always be 19 .csv files for each of the 19 currency pairs.

I have another program which i use to save the currency charts as .csv files.
This program is my trading platform.

I want these .csv files to stay in the charts folder (and not get deleted at all).

If i use my trading platform to resave updated currency charts then they will just overwrite the existing files already in the <charts> folder.

(hope you follow so far).

Now on my main spreadsheet. I have a MAX and a MIN formula which takes the Highest and Lowest prices from Column D in these Currency Charts. Example of my formula is below:

for the highest price
=IF($A5="Select","",MAX(INDIRECT(SUBSTITUTE($A5,"/","")&"!d:d")))

for the lowest price
=IF($A5="Select","",MIN(INDIRECT(SUBSTITUTE($A5,"/","")&"!e:e")))

SO - here we go now.

Basically - Everytime my main workbook is re-opened.
I would like it to SYNC with my Charts folder.

What it needs to do is Delete the array perhaps of the 19 sheets in the main workbook. This is only if its not possible to do a replace later.

Please note - i have OTHER sheets in my main workbook which i DO NOT want to be deleted. So perhaps if i specify the 19 sheets somewhere - this might be better. What do you think?

Ok - now if deleted then next stage will be to find all 19 .csv files in charts folder, move to main workbook, and hide.

Thats it.

Now - next time i re-open the main workbook - same thing should happen.
So that i am ALWAYS updated with the latest High and Low prices for each currency pair.

Hope this all makes sense.

We could, of course, bypasss the delete sheets part if its possible to just replace once we move (but i suspect this will not work and that it might rename the sheet and put a (2) after the sheetname, etc...

Hope this all makes better sense now.
Please let me know and THANKS SO MUCH in advance...

 

[jindon]

Hi there.
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....

try

Sub getcsv()
Dim myFolder As String, fn As String
myFolder = CreateObject("WScript.Shell").SpecialFolders("desktop") & _
            "\Positions\Charts"
fn = Dir(myFolder & "\*.csv")
If fn = "" Then
    MsgBox "No csv file in " & myFolder
    Exit Sub
End If
Do While fn <> ""
    With Workbooks.Open(myFolder & "\" & fn)
        .Sheets(1).Copy _
        After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = _
                Replace(.Sheets(1).Name, "m1440","")
        .Close False
    End With
    fn = Dir
Loop
End Sub

 

[xenou]

Hi Jindon, Slim,

So I think my only worry is should we be deleting the old sheets (somehow) or copy over them...(rather than just plopping the new sheets from the .csv to the end of the main workbook)...

Does either of our solutions (code) do the trick? -- back up everything of course before you test!

AB

---------------------------------------------------------------------------
Edit:
Regarding Sheet Names:
Yes, it would be nice to have those. If they are exactly the same as the csv filenames or can be "parsed" out of those filenames even better.

Could you post: The 19 sheet names and the 19 filenames, or enough of them that the pattern becomes clear?

I'm not sure why but I'd rather copy the data into the sheets then delete/add sheets repeatedly - seems cleaner to me although I'm not sure it makes a difference.
---------------------------------------------------------------------------

 

[slimimi]

Hi Alexander, the ideal situation would be to replace over the old sheets (if thats possible). if this is not possible - then it would be best to delete the old sheets first and then plop the new sheets from the charts folder to the end of the main workbook.

Hope that makes sense.