How to get to a computer on our network using code

[daniels012]

I have code below that works well most of the time. One of the cases below will not work. It insists on requiring a name and password.
(note: it is the only Windows 2000 operating systems)
I want to enter this in my case line to automatically add the username and password.
Let's say the username is: "dave" and the password is: "happy"
How can I add this to this part of the code?
Case "DJ"
strfilename = "\\DAVEJONES\DavesProposals\" & strfilename

Sub Save_and_SaveSalesman()


Dim strPath As String, strPath2 As String, CurrPath As String
    
    Dim WB1 As Workbook
    Dim WB2 As Workbook
       
    Set WB1 = ActiveWorkbook
    WB1.Save
    CurrPath = WB1.Path
    
    strfilename = Range("C6").Value & Range("O3").Value & ".xls"
    
    strPath = "C:\Documents and Settings\Owner\My Documents\Completed Proposals\"
    strPath2 = "C:\Documents and Settings\Owner\My Documents\Surface Systems\"
    
    On Error Resume Next
    
    On Error GoTo 0
    Set WB2 = Workbooks.Open(Filename:=strPath2 & "Proposal for XL.xls")
    
    Select Case WB1.Sheets("FRONT").Range("C2").Value
        Case "MD"
            strfilename = "\\MIKESRGATEWAY\MikesProposals\" & strfilename
            
        Case "TD"
            strfilename = "\\Tomsblackibm\TomsProposals\" & strfilename
            
        Case "DJ"
            strfilename = "\\DAVEJONES\DavesProposals\" & strfilename
            
        Case "CP"
            strfilename = "\\Chuckscomputer\daily\" & strfilename
            
    End Select
    
    WB1.SaveCopyAs Filename:=strfilename
    
    WB1.ActiveSheet.Shapes("Button 53").Visible = False
    
    ChDir CurrPath
    
    Application.ScreenUpdating = True
    
    WB1.Close
End Sub

Thank you for any help!!
Michael

 

[daniels012]

I saw something like this on a thread I searched in. Can this be applied?

, Item, UserName:="admin", Password:="password"

Or in my case:

, Item, UserName:="dave", Password:="happy"

Michael

 

[Oorang]

SaveCopyAs does not accept a username password argument so it wouldn't do you any good anyway. You would need to modify your method to use SaveAs. In the alternative you can use the filesystem object to copy files without having to worry about password restrictions. I would post modified code for you, but I was having trouble intuiting the purpose of your code.

 

[daniels012]

Oorang,
Thank You for responding!!

Purpose of my code:
When I hit a button my code is executed.

1) Save the file "Proposal for XL"

2) then save as:
strfilename = Range("C6").Value & Range("O3").Value & ".xls"

3) onto the Directory & Path for "Completed Proposals"

4) And copy this same named file to the CASE Salesman with the name in cell "C2" on the active workbook.

5) Save the copied workbook with the new name.

6) I need the original file to be reopened "Proposal for XL"

Is this clearer?

If I need to explain each line in detail, let me know.
I know in the past I had issues when I did not open the "Proposal for XL" before I closed the other file.

Michael

 

[Oorang]

Yup. All you need to do is save the file then copy it using the filesystem object. So just do wb1.save or whatever then "FileCopy wb1.FullName, strPath".

 

[daniels012]

Had to go out of town for awhile...
Not sure what you mean?

All you need to do is save the file then copy it using the filesystem object.

When I try to save:

WB1.SaveCopyAs Filename:=strfilename

It gives the error!

Unless you mean to save it to a temporary file somewhere then copy and paste it to the directory? with username and Password ?

What kind of code for all that??

Michael

 

[Oorang]

I just said That method can't do the job
you need to use FileCopy:

FileCopy wb1.FullName, strPath

If you are confused as to what it does just type FileCopy in the VBE move the cursor onto the word FileCopy you just typed and press F1.

 

[daniels012]

I get a runtime error 70
permissin denied

 

[Oorang]

*sigh* Sorry that is my fault. I forgot that the vba version of filecopy will not work if the file is in use. The Microsoft Scripting Runtime version has no such restrictions. Try this:

Option Explicit
Sub Test()
fSaveCopy ThisWorkbook.FullName, "C:\Test\"
End Sub
Sub fSaveCopy(sFilePath As String, sTargetFolder As String, Optional bOverwrite As Boolean)
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
fso.CopyFile sFilePath, sTargetFolder, bOverwrite
End Sub

 

[daniels012]

Oorang,
I am so green when it comes to coding. I have no idea where to put the code you provided for me. I have my code listed above. Is there a certain spot to enter this code?

Thank You,
Michael