Solved

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

Posted on 2013-06-26
14
604 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 ]
ID: 39279176
Hi,

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

BFN,

fp.
0
 
LVL 39

Expert Comment

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

Thomas
0
 

Author Comment

by:CC10
ID: 39279588
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 ]
ID: 39279895
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
ID: 39280449
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
ID: 39281614
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39287844
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
ID: 39297029
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
ID: 39312931
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
ID: 39314169
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
ID: 39316238
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
ID: 39317881
Thanks very much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A short article about a problem I had getting the GPS LocationListener working.
This is about my first experience with programming Arduino.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

929 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

12 Experts available now in Live!

Get 1:1 Help Now