Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficulties using vba to save to the desktop
I've got a quirky problem. The network admin folks here at my office have a
bit of a strange naming convention for users. If you start as a part timer you get a username that looks something like pt[loc][initials] so for someone who is named John H. Smith starting part time at location number 04, his login would be pt04jhs. The trouble is that if John switches to full time his username changes to ft04jhs but his userprofile still points to the directory C:\documents and settings\pt04jhs\. I want to use vba to export (copy) a sheet from the workbook I'm in to a new workbook, save that workbook to the desktop, and close that workbook without prompting the user. (Incidentally, the rest of the naming convention for the file relies on a combo box on the main menu (frmMainMenu) form called cmbSelectDivision, just in case you're wondering why that's in there.) Here's my code right now: dim NameThatExport as string NameThatExport = "Division" + frmMainMenu.cmbSelectDivision.Value _ + ".xls" Sheets("MyList").Select Sheets("MyList").Copy ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\" + Environ("username") _ + "\Desktop\" + NameThatExport, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False _ Windows(fileNm).Activate Application.CutCopyMode = False ActiveWorkbook.Close False The code works well for users who started full time and remained full time and for users who started part time and remained part time but for those who started part time and switched to full time they get no autonaming. Is there a way to use code to find the desktop other than the way I tried above? Thanks for your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficulties using vba to save to the desktop
oops...
Windows(fileNm).Activate should have read Windows(NameThatExport).Activate That's what I get for typing the code from memory rather than copying and pasting. "ArielZusya" wrote: I've got a quirky problem. The network admin folks here at my office have a bit of a strange naming convention for users. If you start as a part timer you get a username that looks something like pt[loc][initials] so for someone who is named John H. Smith starting part time at location number 04, his login would be pt04jhs. The trouble is that if John switches to full time his username changes to ft04jhs but his userprofile still points to the directory C:\documents and settings\pt04jhs\. I want to use vba to export (copy) a sheet from the workbook I'm in to a new workbook, save that workbook to the desktop, and close that workbook without prompting the user. (Incidentally, the rest of the naming convention for the file relies on a combo box on the main menu (frmMainMenu) form called cmbSelectDivision, just in case you're wondering why that's in there.) Here's my code right now: dim NameThatExport as string NameThatExport = "Division" + frmMainMenu.cmbSelectDivision.Value _ + ".xls" Sheets("MyList").Select Sheets("MyList").Copy ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\" + Environ("username") _ + "\Desktop\" + NameThatExport, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False _ Windows(fileNm).Activate Application.CutCopyMode = False ActiveWorkbook.Close False The code works well for users who started full time and remained full time and for users who started part time and remained part time but for those who started part time and switched to full time they get no autonaming. Is there a way to use code to find the desktop other than the way I tried above? Thanks for your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficulties using vba to save to the desktop
Here is one option to return the path to the current user's desktop as a
string. Set objShell = CreateObject("Shell.Application") Set objFolderDsk = objShell.Namespace(&H10&) strDsk = objFolderDsk.Self.Path MsgBox strDsk Set objShell = Nothing Steve "ArielZusya" wrote in message ... I've got a quirky problem. The network admin folks here at my office have a bit of a strange naming convention for users. If you start as a part timer you get a username that looks something like pt[loc][initials] so for someone who is named John H. Smith starting part time at location number 04, his login would be pt04jhs. The trouble is that if John switches to full time his username changes to ft04jhs but his userprofile still points to the directory C:\documents and settings\pt04jhs\. I want to use vba to export (copy) a sheet from the workbook I'm in to a new workbook, save that workbook to the desktop, and close that workbook without prompting the user. (Incidentally, the rest of the naming convention for the file relies on a combo box on the main menu (frmMainMenu) form called cmbSelectDivision, just in case you're wondering why that's in there.) Here's my code right now: dim NameThatExport as string NameThatExport = "Division" + frmMainMenu.cmbSelectDivision.Value _ + ".xls" Sheets("MyList").Select Sheets("MyList").Copy ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\" + Environ("username") _ + "\Desktop\" + NameThatExport, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False _ Windows(fileNm).Activate Application.CutCopyMode = False ActiveWorkbook.Close False The code works well for users who started full time and remained full time and for users who started part time and remained part time but for those who started part time and switched to full time they get no autonaming. Is there a way to use code to find the desktop other than the way I tried above? Thanks for your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficulties using vba to save to the desktop
Thanks Steve. Incidentally, is there any disadvantage to using this method
vs using the environ method? After posting I changed my search tactic and discovered a list of environ variables and discovered I could use "userprofile" to return everything before "\desktop\" but before I decide which method to use I thought I'd ask if one is better or faster or otherwise preferable. Thanks! "Steve Yandl" wrote: Here is one option to return the path to the current user's desktop as a string. Set objShell = CreateObject("Shell.Application") Set objFolderDsk = objShell.Namespace(&H10&) strDsk = objFolderDsk.Self.Path MsgBox strDsk Set objShell = Nothing Steve "ArielZusya" wrote in message ... I've got a quirky problem. The network admin folks here at my office have a bit of a strange naming convention for users. If you start as a part timer you get a username that looks something like pt[loc][initials] so for someone who is named John H. Smith starting part time at location number 04, his login would be pt04jhs. The trouble is that if John switches to full time his username changes to ft04jhs but his userprofile still points to the directory C:\documents and settings\pt04jhs\. I want to use vba to export (copy) a sheet from the workbook I'm in to a new workbook, save that workbook to the desktop, and close that workbook without prompting the user. (Incidentally, the rest of the naming convention for the file relies on a combo box on the main menu (frmMainMenu) form called cmbSelectDivision, just in case you're wondering why that's in there.) Here's my code right now: dim NameThatExport as string NameThatExport = "Division" + frmMainMenu.cmbSelectDivision.Value _ + ".xls" Sheets("MyList").Select Sheets("MyList").Copy ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\" + Environ("username") _ + "\Desktop\" + NameThatExport, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False _ Windows(fileNm).Activate Application.CutCopyMode = False ActiveWorkbook.Close False The code works well for users who started full time and remained full time and for users who started part time and remained part time but for those who started part time and switched to full time they get no autonaming. Is there a way to use code to find the desktop other than the way I tried above? Thanks for your help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
difficulties using vba to save to the desktop
I'd probably use environ since it would spare the overhead of creating the
Shell.Application object. I work more with vbs than VBA so I tend to be more familiar with the tools available in script and sometimes overlook what is readily available in VBA. Steve "ArielZusya" wrote in message ... Thanks Steve. Incidentally, is there any disadvantage to using this method vs using the environ method? After posting I changed my search tactic and discovered a list of environ variables and discovered I could use "userprofile" to return everything before "\desktop\" but before I decide which method to use I thought I'd ask if one is better or faster or otherwise preferable. Thanks! "Steve Yandl" wrote: Here is one option to return the path to the current user's desktop as a string. Set objShell = CreateObject("Shell.Application") Set objFolderDsk = objShell.Namespace(&H10&) strDsk = objFolderDsk.Self.Path MsgBox strDsk Set objShell = Nothing Steve "ArielZusya" wrote in message ... I've got a quirky problem. The network admin folks here at my office have a bit of a strange naming convention for users. If you start as a part timer you get a username that looks something like pt[loc][initials] so for someone who is named John H. Smith starting part time at location number 04, his login would be pt04jhs. The trouble is that if John switches to full time his username changes to ft04jhs but his userprofile still points to the directory C:\documents and settings\pt04jhs\. I want to use vba to export (copy) a sheet from the workbook I'm in to a new workbook, save that workbook to the desktop, and close that workbook without prompting the user. (Incidentally, the rest of the naming convention for the file relies on a combo box on the main menu (frmMainMenu) form called cmbSelectDivision, just in case you're wondering why that's in there.) Here's my code right now: dim NameThatExport as string NameThatExport = "Division" + frmMainMenu.cmbSelectDivision.Value _ + ".xls" Sheets("MyList").Select Sheets("MyList").Copy ActiveWorkbook.SaveAs Filename:= _ "C:\Documents and Settings\" + Environ("username") _ + "\Desktop\" + NameThatExport, FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False _ Windows(fileNm).Activate Application.CutCopyMode = False ActiveWorkbook.Close False The code works well for users who started full time and remained full time and for users who started part time and remained part time but for those who started part time and switched to full time they get no autonaming. Is there a way to use code to find the desktop other than the way I tried above? Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Desktop Save Question | Excel Discussion (Misc queries) | |||
Automatically Save a file on the Desktop | Excel Programming | |||
Save to default desktop | Excel Discussion (Misc queries) | |||
Save .xls as .txt In DeskTop | Excel Programming | |||
How do I save to the desktop across OS's? | Excel Programming |