Environment variable question

Phone_Guy

New Member
Joined
Nov 5, 2009
Messages
19
I'm trying to use an environment variable ( %userName%) so when the user of the spreadsheet saves the macro, it directs it to the folder's that were created with a batch file (md "%userprofile%\desktop\XXXXX) I can get it to save to a specific file but I'm not having any luck using the variable. Any Ideas?


Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail()
Dim FileName As String
FolderName = Environ("UserName")
Dim Today As Date
Today = Date

If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "There is more then one sheet selected," & vbNewLine & _
"be aware that every selected sheet will be published"
End If

code(sheet not have to be active then)
FileName = RDB_Create_PDF(ActiveSheet, "c:\documents and settings\%username%\Desktop\Time_Sheets\" & Range("b35") & ".pdf", True, False)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
TRy

Code:
FileName = RDB_Create_PDF(ActiveSheet, "c:\documents and settings\" & FolderName & "\Desktop\Time_Sheets\" & Range("b35") & ".pdf", True, False)
 

Phone_Guy

New Member
Joined
Nov 5, 2009
Messages
19
Thanks for the reply. I'm now getting "variable not defined" error. It stops at this line:"


FolderName = Environ("UserName")
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
If you have Option Explicit declared at the top of your module (which you should as it's a good idea) then you need to explicitly declare your variable in your routine ie:

Code:
Dim FolderName As String
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If Option Explicit is set (good idea) you need to declare all your variables:

Dim FolderName as String

or use FileName, which you have already declared.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,551
Messages
5,636,966
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top