Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 15th, 2003, 11:06 AM
Registered User
Join Date: Sep 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Strange error when running macro in multiple files


I have written a macro to save a file in 3 different locations (one being an HTM file as well). I have several schedule files (created by doing File Save As from an original) that this macro will be applied to. I have inserted the macro into each file and created a Macro Menu item which has been mapped to the "This Workbook" macro item that i have written. (End Background, Begin Problem/Question) When I run this macro from one sheet, close that workbook, open a different workbook and run the macro, my macro looks for the previous file name. I am not "quite" sure if the problem is code related or tied to the menu assignment. I have included the code for reference and hope someone may have a better way of doing this.

Sub SaveHTML() 

    Dim conNetPath As String 
    Dim conHomePath As String 
    Dim conBUPath As String 
    Dim fName As String 
    Dim HTMLFile As String 
    conNetPath = "\\atlnewsf04\Sudhir Keep\Stuff\skeds\" 
    conBUPath = "c:\Schedules\" 
    conHomePath = ActiveWorkbook.FullName 
    fName = ActiveWorkbook.Name 
    HTMLFile = Replace(fName, "xls", "htm") 

    Application.DisplayAlerts = False 

    ActiveWorkbook.SaveAs Filename:= _ 
        conNetPath & HTMLFile, FileFormat:=xlHtml, _ 
        ReadOnlyRecommended:=False, CreateBackup:=False 

    ActiveWorkbook.SaveAs Filename:= _ 
        conBUPath & fName, _ 
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
        ReadOnlyRecommended:=False, CreateBackup:=False 

'MsgBox conHomePath 
    ActiveWorkbook.SaveAs Filename:= _ 
        conHomePath, _ 
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
        ReadOnlyRecommended:=False, CreateBackup:=False 

    Application.DisplayAlerts = True 
End Sub

When you call this macro from the first file, it works fine. If you close that file and open another file with the same macro and try to run it, it looks for the previously opened file.

Not sure where I goofed but this is in the Modules section for each spreadsheet

Scott Medaugh

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exception Error While Running XSL.Files RICHBIRD XSLT 1 June 15th, 2006 02:58 AM
Probs while running macro anukagni Excel VBA 2 February 2nd, 2006 03:29 AM
running macro/user form from other excel files kliu9 Excel VBA 2 April 7th, 2005 11:06 AM
Multiple delete of files with FSO error nikosdra Classic ASP Professional 1 July 15th, 2003 06:24 PM
Multiple delete of files with FSO error nikosdra Classic ASP Basics 1 July 15th, 2003 10:04 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.