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

 

[jindon]

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

AB

Change

Loop
End Sub

to

Loop
Kill myFolder & "*.csv"
End Sub

 

[slimimi]

Hi jindon - i am getting very confused

Ok i have gone back to your code and tried it.

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

</pre>
It gets the 1st .CSV file in folder which is AUDCADm1440 and creates a sheet at the end of the workbook but then it fires an error :

Automation Error

and then it highlights this line when i debug

ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = _
Replace(.Sheets(1).Name, "m1440", "")


Also jindon. I would like the new sheet to REPLACE the existing sheet with same name in the workbook. Is this possible please?

 

[slimimi]

Let me just explain what i want to keep in my workbook (if this helps).

Sheet1 (positions)
Sheet10 (Sheet2)
Sheet23 (price)

These above sheets must not change or be affected by the code.
I wish they were nicely arranged (ie, Sheet1 = positions, Sheet2 = Sheet2, Sheet3 = price) but i am too scared to try and rearrange them because i dont want all the other macros in my workbook to get messed up (my debugging skills are VERY limited indeed).

Hope this helps.
Thanks in advance.

Really want to get this code to work sigh

All i wanted really is that everytime i open this workbook and fire this macro - it grabs all .csv files from <charts> folder, moves them to main workbook (replacing existing files with same name) and then hides all those 19 sheets.

The changing of the sheetname from "m1440" to "" is not really that important because i can always change the formula on my main sheet which accesses the data from these sheets.

Hope this helps and makes sense.

Thanks a MILLION in advance.

 

[jindon]

Automation Error ?
Then just delete tha line

ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = _
Replace(.Sheets(1).Name, "m1440","")

 

[slimimi]

I have tried this already Jindon but i am still getting an automation error.
Also i need the code to Replace the sheets with the same name in the workbook rather than adding AUDCADm1440 (2) for example...

Hope this makes sense.
Thanks in advance.

 

[jindon]

AUDCADm1440 (2)

Do you have sheet named "AUDCADm1440" already in ThisWorkbook ?

 

[slimimi]

Hi Jindon. Yes I do - let me explain.

I have a workbook which has all these sheets inside it (listed below).

So everytime i perform your code - i want to create 19 sheets (based on the 19 .csv files in (chart)<charts> folder) and then replace the OLD 19 sheets in my workbook with the new ones.

Does this help to make things a bit clearer?
Sorry for any confusion cause.

Here is the list of 19 sheets (if it helps) :
Please note : these are the ONLY sheets that i want to be replaced in my workbook.
The other 3 sheets which i mentioned above (positions, sheet2 and price) i do not want to be affected at all.



<table x:str="" style="border-collapse: collapse; width: 50pt;" width="66" border="0" cellpadding="0" cellspacing="0"><col style="width: 50pt;" width="66"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 50pt;" width="66" height="17">AUD/CADm1440</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">AUD/JPYm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">AUD/NZDm1440</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">AUD/USDm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">CHF/JPYm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">EUR/AUDm1440</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">EUR/CADm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">EUR/CHFm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">EUR/GBPm1440</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">EUR/JPYm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">EUR/USDm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">GBP/CHFm1440</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">GBP/JPYm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">GBP/USDm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">NZD/JPYm1440</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">NZD/USDm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">USD/CADm1440</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl24" style="height: 13.5pt;" height="18">USD/CHFm1440</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">USD/JPYm1440
</td> </tr> </tbody></table></charts>

 

[jindon]

OK then
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
Application.DisplayAlerts = False
Do While fn <> ""
    With Workbooks.Open(myFolder & "\" & fn)
        On Error Resume Next
        ThisWorkbook.Sheets(.Sheets(1).Name).Delete
        On Error GoTo 0
        ThisWorkbook.Sheets.Add() before:= ThisWorkbook.Sheets(1)
        ThisWorkbook.Sheets(1).Name = Replace(.Sheets(1).Name, "m1440","")
        .Sheets(1).Cells.Copy ThisWorkbook.Sheets(1).Cells(1,1)
        .Close False
    End With
    fn = Dir
Loop
Application.DisplayAlerts = True
End Sub

 

[slimimi]

Thanks Jin - i am getting an error though on this line.

ThisWorkbook.Sheets.Add() before:= ThisWorkbook.Sheets(1)

Compile Error
Syntax error

 

[xenou]

Maybe change:
ThisWorkbook.Sheets.Add() before:= ThisWorkbook.Sheets(1)

To:
ThisWorkbook.Sheets.Add Before:=ThisWorkbook.Sheets(1)

--No parenthesis...might work, possibly