Solved

Problem with "C:\Users\" & Environ("Username") & "\

Posted on 2013-06-26
14
589 Views
Last Modified: 2013-07-11
I have a macro on one computer where I changed the user name from
"C:\Users\Centa\
to
"C:\Users\" & Environ("Username") & "\

the macro ran without problem

I have now tried to run the macro on another computer but it does not work and the Msg box pops up.

Dim newName
Dim xlsmWB As String
xlsmWB = "C:\Users\" & Environ("Username") & "\Dropbox\Centa\xxx.xlsx"

If Dir(xlsmWB) = "" Then
'File not found on expected path / users machine handle error
    MsgBox "You machine is not properly configured to run this code", vbCritical, "Error"
Else
    Workbooks.Open Filename:=xlsmWB, UpdateLinks:=0
End If

I am running Office 2007 enterprise on both computers. Both files are in Dropbox, where the macro is in the first file and it should copy a worksheet from the first file to the second. Therefore I can open the first file from different computers but the macro has a problem with the path to the second file as each computer has a different administrator name.

Do you have any ideas what the problem could be?

Thanks,
CC
0
Comment
Question by:CC10
  • 6
  • 3
  • 2
  • +1
14 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
Hi,

What is the username [ Environ("Username") ] value on the second machine?

BFN,

fp.
0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
Different windows environments in the two computers? Do both paths actually exist?

Thomas
0
 

Author Comment

by:CC10
Comment Utility
Windows 7 Professional and Office 2007 on both.

On the computer where the macro does not work: (Lenovo Laptop)


Location of File1:
C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\Trade Input.xlsm

Location of File 2:
C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\TradeTickets_June2013.xlsx

On the computer where the macro does  work: (Dell PC)
Location of File1:

C:\Users\Christopher Charlton\Dropbox\Centa\Centa FXDP\Trader Journal\Trade Input.xlsm

Location of File 2:
C:\Users\Christopher Charlton\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\Trade Input.xlsm
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
The reason I asked about the username of the second person is because I suspected there was a space character within it:

"Christopher Charlton".

I would suggest converting the fully-qualified filename to an 8.3 file format using the "GetShortPathName" Kernel32 Windows Application Programming Interface [API] routine such as within the example below:

[ http://www.freevbcode.com/ShowCode.asp?ID=3212 ]

Notably...

Private Declare Function GetShortPathName Lib "kernel32" _
   Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
   ByVal lpszShortPath As String, ByVal cchBuffer As Long) _
   As Long

Dim strBuffer As String * 255
Dim lngReturnCode As Long

lngReturnCode = GetShortPathName(strFilename, strBuffer, 255)
ShortPath = Left$(strBuffer, lngReturnCode)

Where strFilename is your variable: xlsmWB.
0
 

Author Comment

by:CC10
Comment Utility
If I insert your code after the End Sub of my macro and insert a End Function at the bottom, I get an error message saying:
Compile error:  Only comments  may appear after End Sub etc.......

The macro I use is as follows: Can you help adding the Function correctly?

Thanks,
CCC

Sub CopyTradesToTraderLog()

Dim newName
Dim xlsmWB As String
xlsmWB = "C:\Users\" & Environ("Username") & "\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\TradeTickets_June2013.xlsx"

Range("A:C").Select
    Selection.Copy
If Dir(xlsmWB) = "" Then
'File not found on expected path / users machine handle error
    MsgBox "You machine is not properly configured to run this code", vbCritical, "Error"
Else
    Workbooks.Open Filename:=xlsmWB, UpdateLinks:=0
End If
   
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
     
    newName = Range("c8").Value2 & "-" & Range("C35").Value2
    ActiveSheet.Name = newName
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub
0
 

Author Comment

by:CC10
Comment Utility
One point. xlsmWB = "C:\Users\" & Environ("Username") & "\Dropbox\Centa\xxx.xlsx"

this works on the computer with the Username: Christopher Charlton

It does not work on the Laptop with the Username: Centa
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
Hi,

Are you able to attach the workbook you are using to this thread so I may review what you have already & apply the necessary additions for using the "GetShortPathName" routine directly?

Thanks.

BFN,

fp.
0
 

Author Comment

by:CC10
Comment Utility
Hello, sorry for the delay in answering but I was travelling. I have attached the workbook that I use.
The macros are in module 4.
if you look at the 3 macros you will see the different paths from each computer:
'Workbooks.Open Filename:="C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\TradeTickets_June2013.xlsx"
    'Workbooks.Open Filename:="C:\Users\Christopher Charlton\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\TradeTickets_June2013.xlsx"
    'Workbooks.Open Filename:="D:\Documents and Settings\Christopher\My Documents\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\TradeTickets_June2013.xlsx"

In the first macro:  Sub CopyTradesToTraderLog(), I tried to insert your code.
TRADE-INPUT-TEST-CC.xlsm
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
Is it more reliable to use this:
xlsmWB = Environ("userprofile") & "\Dropbox\Centa\xxx.xlsx"

and then perhaps enclose it in quotes when opening it, as in
    Workbooks.Open Filename:="""" & xlsmWB & """", UpdateLinks:=0

Regards,

Rob.

Dim newName
Dim xlsmWB As String
xlsmWB = Environ("userprofile") & "\Dropbox\Centa\xxx.xlsx"

If Dir(xlsmWB) = "" Then
'File not found on expected path / users machine handle error
    MsgBox "You machine is not properly configured to run this code", vbCritical, "Error"
Else
    Workbooks.Open Filename:="""" & xlsmWB & """", UpdateLinks:=0
End If

Open in new window

0
 

Author Comment

by:CC10
Comment Utility
I used this:
xlsmWB = Environ("userprofile") & "\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\TradeTickets_TEST2.xlsx"

But I get a a runtime error 104
saying
"C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\TradeTickets_TEST2.xlsx"

could not be found. Check spelling etc......

But the path is correct, so the formula works.

If I use this path in the macro, the file opens correctly.
Workbooks.Open Filename:="C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\TradeTickets_2013\TradeTickets_TEST2.xlsx"
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 400 total points
Comment Utility
Hmm...try changing
    Workbooks.Open Filename:="""" & xlsmWB & """", UpdateLinks:=0

to
    Workbooks.Open Filename:=xlsmWB, UpdateLinks:=0

now that the path is correct.

Rob.
0
 

Author Closing Comment

by:CC10
Comment Utility
Thanks very much
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now