• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

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

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
CC10
Asked:
CC10
  • 6
  • 3
  • 2
  • +1
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

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

BFN,

fp.
0
 
nutschCommented:
Different windows environments in the two computers? Do both paths actually exist?

Thomas
0
 
CC10Author Commented:
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
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!

 
[ fanpages ]IT Services ConsultantCommented:
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
 
CC10Author Commented:
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
 
CC10Author Commented:
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
 
[ fanpages ]IT Services ConsultantCommented:
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
 
CC10Author Commented:
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
 
RobSampsonCommented:
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
 
CC10Author Commented:
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
 
RobSampsonCommented:
Hmm...try changing
    Workbooks.Open Filename:="""" & xlsmWB & """", UpdateLinks:=0

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

now that the path is correct.

Rob.
0
 
CC10Author Commented:
Thanks very much
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now