VBA move active file from one save location to another

[jjlafond]

Hello,

I currently have an active workbook being saved (using VBA)to the desktop for easy access. I would like to create a macro that (on a command button) deletes/moves the active workbook from the desktop to a different folder (C:\Users\jjlafond\Documents\).

I'm not at all sure how to go about doing this moving process. Is there a specific vba code for moving current files? Or a combination of SaveAs to the new location & somehow delete from the old?

 

[Darren Bartrup]

A couple of ways I can think of:

#1 saves the workbook in a different location and deletes the original. The workbook has to be open for it to be saved, etc though.

Public Sub Move()

    ThisWorkbook.SaveAs "C:\Documents and Settings\crladmin.ADMINNOT\Desktop\Off Network Folder\Book6.xlsm"
    Kill "C:\Documents and Settings\crladmin.ADMINNOT\Desktop\Book6.xlsm"


End Sub

#2 will move and rename a file (any type) without it being open. Doubt it will work on Thisworkbook though.

'----------------------------------------------------------------------
' MoveFile
'
'   Moves the file from FromFile to ToFile.
'   Returns True if it was successful.
'----------------------------------------------------------------------
Public Function MoveFile(FromFile As String, ToFile As String) As Boolean


    Dim mFSO As Object
    
    Set mFSO = CreateObject("Scripting.FileSystemObject")


    On Error Resume Next
    mFSO.MoveFile FromFile, ToFile
    MoveFile = (Err.Number = 0)
    Err.Clear
    
    Set mFSO = Nothing
    
End Function


Sub TestMove()


    MoveFile "C:\Documents and Settings\crladmin.ADMINNOT\Desktop\July Rcvd.xlsx", _
             "C:\Documents and Settings\crladmin.ADMINNOT\Desktop\Off Network Folder\July Rcvd1.xlsx"


End Sub

 

[jjlafond]

Thank you! I used Code 1, and added extra "features" with the process. It works PERFECTLY.

 

[Darren Bartrup]

Glad it worked.
As a bit of a follow up I'd check this site for saving Excel in various formats:
Use VBA SaveAs in Excel 2007-2013

I meant to mention this on Friday, but forgot.

 

[jjlafond]

Thank you. I had the format type in my SaveAs line, but until now I didn't know why

 

[dwhitey1124]

Any idea why this isn't working?

Sub TestMove()

Dim FNameOld           As String
    Dim FNameNew           As String
     
    FNameOld = Sheet1.Range("C2").Value
    FNameNew = Sheet1.Range("C3").Value

    MoveFile FNameOld, FNameNew
End Sub

Thank you. I had the format type in my SaveAs line, but until now I didn't know why

 

[Darren Bartrup]

I take it you've got the MoveFile function in the module somewhere.

Does both C2 & C3 contain the full path including the file name?

E.g:
C:\My Documents\Workbook 1.xls in C2
and
C:\My Documents\New Folder\Workbook With A New Name 1.xls in C3?

 

[Kurt]

I take it you've got the MoveFile function in the module somewhere.

Does both C2 & C3 contain the full path including the file name?

E.g:
C:\My Documents\Workbook 1.xls in C2
and
C:\My Documents\New Folder\Workbook With A New Name 1.xls in C3?

I found this old post while searching for info. I modified the code to this:

Sub TestNewTabInIE()
' modified from code originally found at:
'http://www.ozgrid.com/forum/showthread.php?t=174692
Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")

With IE
    .Visible = True
    'While .ReadyState <> 4 'READYSTATE_COMPLETE
    '    DoEvents
    'Wend
    '# Navigate to one URL
    '.Navigate "http://google.com"
    '# Navigate to another URL, in a new tab.
    '.Navigate "Http://yahoo.com", CLng(2048)
    '=== My company URL here which is a SAP application.  How do I enter my user id and password via vba so it will open automatically
End With
End Sub

This is the question from my comment:

How do I enter my user id and password via vba so it will open automatically?