Solved

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

Posted on 2013-06-26
14
640 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

749 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