How to do it?

[Marc - Pierre Gubler]

Hi

I want do save my current document in Excel,
close the file, copy it to another folder and reopen it.

I don't know how to do.
In Word my code is correct, in Excel I can close the file, but I can't
reopen it.

Who can help me?


Here's my code:
'---------------------------------------------------------------------------
---
Option Explicit
'---------------------------------------------------------------------------
---
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
'---------------------------------------------------------------------------
---
Public Property Get TempDirectory() As String
Const MAXPATH As Long = 260
Dim sBuffer As String

sBuffer = VBA.String(MAXPATH, 0)
If GetTempPath(Len(sBuffer), sBuffer) Then
TempDirectory = VBA.Left(sBuffer, VBA.InStr(sBuffer, vbNullChar) - 1)
End If
End Property
'---------------------------------------------------------------------------
---
Sub Start()
Dim strFullFilenameXL As String
Dim strShortFilenameXL As String
Dim strFilenameTmp As String
Dim strFilePath As String
Dim varResult As Variant
Dim intLength As Integer

On Error GoTo Ende

'Save file
ActiveWorkbook.Save

'Get full filename and path
strFullFilenameXL = ActiveWorkbook.FullName

'Get filepath
strFilePath = ActiveWorkbook.Path

'Get length of filename
intLength = Len(strFullFilenameXL) - Len(ActiveWorkbook.Path) - 1
'Get filename
strShortFilenameXL = Right$(strFullFilenameXL, intLength)

Stop

'--- I CAN DO WHAT I WANT TO REOPEN THE FILE; NOTHING WORKS --- '
ActiveWorkbook.Close
'varResult = Application.Dialogs(xlDialogOpen).Show(strFullFilenameXL,
False, False)

Stop
'Set Temp-filename
strFilenameTmp = TempDirectory + strShortFilenameXL

'Spaces in filename are not allowed, so they are replaced with _
strFilenameTmp = Replace(strFilenameTmp, " ", "_")

'Copy File To Temp-Folder
FileCopy strFullFilenameXL, strFilenameTmp

'Open RecentFile opened
Application.RecentFiles.Item(1).Open

'Kill Tempfile
Kill (strFilenameTmp)

Exit Sub

Ende:
varResult = MsgBox("An Error occurred: >>" + Error$ + "<<" + Chr$(13),
vbCritical, "Error", 0, 0)
End Sub
'---------------------------------------------------------------------------
---

Thanks in advance and best regards,
Marc

P.S.: maybe you can send me a mail that you have answered my question?

 

[jaf]

Hi Marc,
Where is the code?
If it is in the workbook you just closed, the code can't run.

 

[Marc - Pierre Gubler]

The Code starts with "Option Explicit"

If it is in the workbook you just closed, the code can't run.

Okay, but who can tell me how to do so that I close the file and reopen it.
The code needs to run under office 2000.

Thanks in advance and best regards,

Marc