+ Reply to Thread
Results 1 to 2 of 2

Macro won't open original file?

Hybrid View

  1. #1
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177

    Cool Macro won't open original file?

    The last instruction of the macro below is to open the original file, but it fails to do so.
    I have no idea why?
    As always, this forums help is greatly appreciated.

    [Begin macro]
    Sub Save_As()
    '
    ' Save_As Macro
    ' Macro recorded 1/3/2006 by rstanich
    '
    ' Keyboard Shortcut: Ctrl+x
    '
    Dim sDcode As String 'Date Code as String
    Dim sScode As String 'Serial Number Code as String
    Dim sFname1 As String 'File Name of Original file as String
    Dim vFname2 As Variant 'File Name of New file as Variable

    Dim bOkToSave As Boolean 'True or False
    Dim sPath As String 'Path variable

    sPath = ThisWorkbook.path 'Get path of file

    ThisWorkbook.Activate
    Worksheets("Sheet2").Select 'Select Work Sheet 2
    sDcode = Range("D2").Value 'Select Cell D2 on Sheet 2
    sScode = Range("C2").Value 'Select Cell C2 on Sheet 2
    sFname1 = sPath & "\" & "0020-48183-fir_Cal-Precision.xls" 'Set File Name of Original file
    vFname2 = sPath & "\" & "0020-48183-fir_Cal-Precision-" & sDcode & "-" & sScode & ".xls" 'Set new file name

    'Create new file name begin
    'Check if exists, exit if exists
    ActiveWorkbook.SaveAs Filename:=vFname2, FileFormat:=xlNormal 'Self checks for existing file
    ActiveWorkbook.Close SaveChanges:=False 'Close new file
    'Open original file
    Workbooks.Open Filename:=sFname1

    End Sub
    [End Macro]
    Regards

    Rick
    Win10, Office 365

  2. #2
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177
    Found one solution, see code below.

    sFname = sPath & "\" & "0020-48183-fir_Cal-Precision.xls" 'Set File Name of Original file
    vFname = sPath & "\" & "0020-48183-fir_Cal-Precision-" & sDcode & "-" & sScode & ".xls" 'Set new file name
    'MsgBox sFname & " " & "Original file name" 'for testing
    'MsgBox vFname & " " & "New file name" 'for testing
    'Create new file name
    'Check if exists, exit if exists 'need code
    ActiveWorkbook.SaveCopyAs vFname 'NEW CODE
    'MsgBox sFname & " " & "Original file should be open" 'for testing

    This wasn't a matter of a macro not opening the original file, but the macro closing the file that did not need closing. Performing a "SaveAs" instead of a "SaveCopyAs" was closing the only excel file that was open. Allthough, I am not sure why I could not open a workbook after the close.
    Last edited by Rick_Stanich; 02-14-2006 at 06:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1