Solved: Changing a filename

[vzachin]

hi,

I have a code that opens a .csv file from the company's intranet.
Data is then copied & pasted from the opened file to the workbook that generated the macro. And then the file is closed.
The file that is opened is referenced by the EndUser that generates the report. e.g. USERNAME.REPORTNAME.csv.
I can run this macro without a problem because I am the one generating the request. However,if somebody else ran this report, they cannot open their own report because the link to the file is hardcoded with my UserName in the coding.

Is there any way to have the EndUser input their USERNAME and then somehow link this USERNAME to the file that gets opened?

[vba]

Workbooks.Open Filename:= _
"http://xxxxxxxxxx.xxxxxx.com/xxxxxx/...REPORTNAME.CSV"
ActiveWindow.Visible = True
Application.DisplayAlerts = False
'=============
Windows("USERNAME.REPORTNAME.CSV").Activate
Cells.Select
Selection.Copy
Windows("ORIGINALREPORTNAME.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Windows("USERNAME.REPORTNAME.CSV").Close
[/vba]


thanks
zach

[Bob Phillips]

[vba]

Dim sUser As String

sUser = Environ("UserName")
Workbooks.Open Filename:= _
"http://xxxxxxxxxx.xxxxxx.com/xxxxxx/xxxxxx/xxxxxx/" & sUser & ".REPORTNAME.CSV"
ActiveWindow.Visible = True
Application.DisplayAlerts = False
'=============
Windows(sUser & ".REPORTNAME.CSV").Activate
Cells.Select
Selection.Copy
Windows("ORIGINALREPORTNAME.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Windows(sUser & ".REPORTNAME.CSV").Close
[/vba]

[vzachin]

hi EL Xld,

thanks for your speedy reply.
I modified my code but i don't understand how and where the EndUser can enter their "username" when running the macro


zach

[CBrine]

zach,
sUser = Environ("UserName")
retrieves the user name from the system, so the user doesn't need to enter thier user name, just need to be signed in as themselves.

HTH
Cal

[vzachin]

hi CBrine,

thanks for the explanation. i see how that works now.
the problem i have now is that the username from the system is in lower case while the report is in upper case. is there a trick around this?

thanks again
zach

[CBrine]

"http://xxxxxxxxxx.xxxxxx.com/xxxxxx/xxxxxx/xxxxxx/" & Ucase(sUser) & ".REPORTNAME.CSV"

This should fix it up.

HTH
Cal

[vzachin]

Cbrine,
That is amazing and so simple!

Thanks again
zach