Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

VB-Excel SaveAs Method, If File Exists, Rename Without Prompting

When using the SaveAs method in VB to save my Excel sheet, if the user entered an existing filename, I want my code to just append a 1 to the end of the filename, save it and move on, no prompting, etc.

Right now if the file exists, a msgbox pops up asking if i want to over-right, if you say no, it asks to save book1. I then want that saved as Filename-1 I want this to happen all behind the scenes.

I know I could probably do some FSO stuff to check but wondering if there is a shorter way, like does the SaveAs throw an error that I can trap?

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Check If A Excel File Exists And To Rename It ?
Can any one help with the code to check if a excel file exists in a folder and to rename it if it does


HELP!! Using VBScript To Call EXCEL's SaveAs Method.
I'm using VBScript (via WSH 1.0) to load an existing EXCEL workbook template (.xlt), makes a few changes to it's contents, then attempts to use .SaveAs to save it to disk with the path+filename specified as a Variant. See code below.

objXL.ActiveWorkbook.SaveAs NewAppDocName
IF (Err.Number <>0) then
Call ReportError("Can't SAVE requested workbook !!",NewAppDocName)
End IF

As VBScipt is late binding it seems not to allow NamedArguments like VB.
If the variant NewAppDocName is replaced with a string literal like .SaveAs "A1" then it works!! Also, placing a " before & after the filename contained in the variant doesn't work either.
If this can't be done using SaveAs, is it possible to change to name of the workbook, then call .Save (same end result that I'm after).

Any help is much appreciated, especially after spending days on this problem!!


Move File Method And Rename Question
I am attempting to move a file and rename it. The code I am currently trying to use is:

[My.Computer.FileSystem.MoveFile("C:My DocumentsFile2*.xls", "C:My DocumentsSalesProcessed2.xls")]

I am getting a compile error.

Any thoughts?, please advise.


File.exists Method...
Anyone know what library the file.exists method is in?

&"Illegal Operation&" On Executing SaveAs Method Of Excel.Worksheet Object
Hi everybody,

I have installed a database application at my client's office. I am using VB 6.0 on Windows 2000 Professional and my client's operating system is Windows 98. The print option of one of my entry modules in the application, creates an Excel Worksheet and saves it with a file name using Excel automation objects. When I compile the EXE file of the application on my computer and copy it to my client's computer, on executing the SaveAs method of the Excel.Worksheet object, the error message "This program has performed an illegal operation and will be shut down..." shows up. But when I compile the EXE file of the application on my client's computer, it works absolutely fine. My client's version of Excel is 2000 and that of mine is 2002. Is that the reason or is it something else?

Used .SaveAs And Excel File Is Bugged
I'm simply opening an existing excel spreadsheet, passing variables into it, and attempting to save it as a new file name.

I've done this two ways:

The first being creating a new sheet, passing values to it and saving it.

When I do it this way the file is created, its named properly and has an .xls extension to it. The file has size to it (not empty) and properties like read/write access are visible. However when you try to open this file excel opens and closes instantly.

The second method was to open an existing file, over-write existing data, and save it as a new name.

When I do it this way the file is created, its named properly and has an .xls extension to it. The file has size to it greater then it should and properties like read/write access are visible. However, when you open the file only excel the application opens and not the spread sheet.

I'm thinking that there is some type of formatting error?

New Problems With SaveAs Method
Hello Everyone,

On further inspection, the SAVEAS method trys to Save the Worksheet back into the Xls - with a new name. If the Sheet name changes - the app won't run!

The MSDN definition of SaveAs says "Saves changes to the sheet (syntax 1) *in a different file* " This is the behavior expected -not saving to the xls.

Private Sub cmdSave_Click()
Dim varFileName As Variant

On Error GoTo 300
ChDir "C:"
varFileName = Application.GetSaveAsFilename("My Report", "Print File (*.prn),*.prn", , "Save Output")
If (VarType(varFileName) = vbString) Then
OWS.SaveAs FileName:=varFileName, FileFormat:=xlTextPrinter
Exit Sub
End If

MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub
So if my Worksheet is named "Output", after the SaveAs it's now called "My Report" - actually taken from GetSaveAsFilename. In debug I can see varFileName is set to "C:My Report.prn"
BTW: OWS is an Excel.Worksheet set to the Worksheet named "Output".


Just noticed that under Tools/Options, there's a "Transition" tab where you can set "Save Excel files as". Mine is set to Microsoft Excel Workbook. All of the FileFormat:= settings are listed there.

Do I need to set this option to "Formatted Text (space delimited)" - which is my SaveAs setting, xlTextPrinter - to stop SaveAs from trying to save the Worksheet back into the Workbook with a new name as stated above?

Problems With SaveAs Method
i have written this piece of code with VB script. It is running behind a custom Oultook form. When the command button is pushed, it opens up an inputbox and then it sends the input to an access database. Now I am trying to save the input to a text file. I get an error saying that "bject does not support this propety or method." Does any one have any suggestions. Thanks

Sub Comment_Click()

Const OLTXT = 0

set conn = CreateObject("ADODB.Connection")
strconn= "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:Documents and Settings
qaamMy Documentsdb1.mdb"
conn.Open strconn
Set rec= CreateObject("ADODB.Recordset")
rec.Open "SELECT * FROM TEST ", conn, 3, 3


comment = InputBox("Please Enter Your Comment", "Additional Comments", "Your Comment Here.")
If comment <> "" Then
rec.Fields("Comment") = comment
End If

comment = Item.UserProperties("Attach1").Value
strname = "attach"
Item.UserProperties("Attach1").SaveAs "C:" & strname &".txt",oltext


End Sub

Prompting User For Filename In Excel ...

I have the following code to prompt a user to save an Excel spreadsheet (when I have finished building it !) :

VB Code:
Do        fname = Application.GetSaveAsFilename    Loop Until fname <> ""

What I need is someone to tell me how I change the default path of the SaveAs box, as it is defaulting to the 'My Documents' folder, and I need 'S:MARGIN'.

Looking under the various properties for 'APPLICATION', but haven't found what I need yet !


How To Suppress Prompting Of Excel While Saving??
  when ever i try to save the file it is prompting to save the changes you to suppress this.thanx in adv.


SaveAs Method And Text Delimiters
Is it possible to have SaveAs use Text Delimiters? Specifically, double quotes (")?

Current code:

Call exWB.SaveAs("c:stuff.csv", xlCSV, , , False, , , , False)

Problem In SaveAs Method Of Workbooks
Hello All,

I am stucked here,i want to save a existing .xls file having filetype html to a excel file having file type code is as bellow

Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("C: emp.xls")
objWorkBook.SaveAs("C:XMLCopy.xls", _

objWorkBook.Close True
Set objWorkBook = Nothing
Set objExcel = Nothing


but it is not working.However if i replace
objWorkBook.SaveAs("C:XMLCopy.xls", _
Excel.XlFileFormat.xlWorkbookNormal) to
it is working fine.
please help me ?
Vijay Saraf.

Check If Folder Exists &amp; Rename Existing?
Hi all,

I have a program which creates a folder called 'Feedback_files' and writes files into the dir. I use the following code to create the folder:

On Error Resume Next
MkDir App.Path & "Feedback_Files"
How is it possible to use this to check if the folder exists, if so rename it to "Feedback_files_backup_1", then create the "Feedback_Files" folder?

thanks for any help with this.

Method &"SaveAs&" Or &"Save&" Of Object &"Excel.Workbook&" Does Not Work.
Hi people.
I did for a long time a program that uses excel automation, so that from a template will create a final file with some data pulled from the database (with ADO). At the end and when the Excel file is ready to be saved I got in a couple of computers some problems. In the whole LAN have XP, but in these 2 computers we have SP2. I have checked the rights and are OK (I have even tried to save the Workbook under the Temp folder and also the same problem).
Working with Office 2003 (SP1).
I have searched in internet about any bugs but I don´t find anything.
Any ideas.
Thanks for your time

FileExists() And Opening File In Excel Doesn't Work - Although File Exists
Hey there,

I have a little helper tool (written in vb6) that opens an excel file specified via the Command-variable, works a little with the content and closes it again. The path the file resides is e.g.: "D:.Net DevelopmentVisual Studio 2005ProjectsSynchronize_VersionPlanVP-Helper ransfer.xls"

Prior to do doing anything, I check whether the file specified via the Command-variable exists:

VB Code:
Dim tmpFSO As FileSystemObject mod_global_var.ExcelFile = CommandSet tmpFSO = CreateObject("Scripting.FileSystemObject") If mod_global_var.ExcelFile = "" Then    'logout - can't continue    MsgBox ("This tool cannot be called directly - closing now!")    'logout user    If Not mod_global_var.objSession Is Nothing Then        mod_global_var.objSession.logout    End If     End ElseIf tmpFSO.FileExists(mod_global_var.ExcelFile) = False Then    MsgBox ("Transfer File not found! Please run the main synchronization tool again to (re-)create a valid transfer file!")    cmd_go.Enabled = FalseElse'do the real work...End

However, the problem is that whenever I call the tool in the directory mentioned above, the Messagebox from the "ElseIf tmpFSO.FileExists[...]" statement pops up - even though the file does exist!

Copying the .exe and .xls file e.g. to the d: main directory and call the tool works perfectly - no more messagebox etc. Why's this? I mean.. why doesn't the FileExist work properly with a path like the one mentioned above?

Best regards and thanks in advance,

Can I Use The Exists Method In VBA?

I am trying to find out whether a particular item of the CustomDocumentProperties collection exists.

Using the Exists method does not seem to work.

Any ideas?


About If These Method Or Property Exists
i have these line of code in visual basic 6:

If modColisão.Colisão(IFF(Object1.LimColActivate = True, Object1.LimColLeft _
+ Object1.Left, Object1.Left), IFF(Object1.LimColActivate = True, Object1 _
.LimColTop + Object1.Top, Object1.Top), IFF(Object1.LimColActivate = _
True, Object1.LimColWidth, Object1.Width), IFF(Object1.LimColActivate = _
True, Object1.LimColHeight, Object1.height), IFF(Object2.LimColActivate <> _
True, Object2.Left, Object2.LimColLeft + Object2.Left), IFF(Object2 _
.LimColActivate <> True, Object2.Top, Object2.LimColTop + Object2.Top), _
IFF(Object2.LimColActivate <> True, Object2.Width, Object2.LimColWidth),_
IFF(Object2.LimColActivate <> True, Object2.height, Object2 _
.LimColHeight)) = True Then....

my question is: if Object2 don't have these property: LimColActivate i recive 1 error, but what value LimColActivate recive?

Checking If Method Of Object Exists
I have a Variant array and only some of its members have the method .accName, so if I pass though the array with a For loop an error is raised on the 'rows' in the array without the method .accName. Is there a way I can check if an object has a method before calling it and raising an error? I don't care for the On Error Resume Next approach either.

Using Exists Method Inside Word VBA? - Please Help :)

I am running some VBA code which creates a custom toolbar inside To prevent the custom tool bar being created every time word is activated, I am trying to use the Exists method to check whether the custom toolbar has already been created.

However, this does not seem to work:

If ActiveDocument.CommandBars.Exists("EMS Tools") Then
MsgBox "Exists"
End If

I am told the Exists method does not exist.

I am nearing desparation and any help would be much appreciated

Many thanks,


How To Check If A File Exists, If Doesn't Exists, Then Download It?
How to check if a file exists, if doesn't exists, then download it?

.close Method On Excel File
After doing a .close on an excel workbook say

VB Code:

i find that when i do a

VB Code:
? myexcelbook is nothing

i get a true which means that the myexcelbook still exists but if i do a

VB Code:

i get

"run-time error '462'

the remote server machine does not exists or is unavailable"

is this an inconsistency?

Method Or Data Member Not Found...BUT IT EXISTS!!!
I have 2 VB EXE and a DLL.
The EXE references the DLL.

In the DLL I have a class called Woof with a function called Fish.

Now I run the DLL in the VB IDE...then reference the VBP project from the EXE, that is also running in a VB IDE, but it beeps and gives me the compile error (see subject)...BUT the function does exist, and the smart auto detect method thingy that vb uses when you type objWoof then a "." shows that Fish exists and also shows you the strings varibales that should be passed.

Now sometimes when I click run it beeps, gives me the error, but when I click run again it works!!! Sometimes it will give the same error for another function on the 2nd attempt, and on the 3rd it will work. But it is refusing to budge just outright says that this method doesn't exist....

It only happens on very large projects!!! Is it something to do with memory, lotus notes or novel running on this stupid NT box???


How To Import An Excel File Into An Access Table With DAO Method

I want to import an excel file into an Access table, not with the TransferSpreadSheet function but using DAO method.

This is because sometimes when I use the TransferSpreadSheet function, the table gets with a hundreds of null records before it is the first record. The help file says it's better to use DAO method instead of the function.

I'm trying to do so but have problems with the code. What I wrote is:

    Set dbs = CurrentDb()
    Set tdfLinked = dbs.CreateTableDef("ExcelTable")

    tdfLinked.Connect = "Excel 8.0;DATABASE=test.xls"

    tdfLinked.SourceTableName = ???? 'What should come here? What's the format to define the SourceTableName???

    dbs.TableDefs.Append tdfLinked

I want the table just the same as the excel file but don't know how to define de SourceTableName

I really don't know how to solve this problem and need urgent help!!!


Am having problem with saveas syntax.

how can i save an excel file to a dbf file using the SaveAs command?


Excel SaveAs

Is it possible to prevent someone to make a 'Save As' in Excel .

I've read many examples using 'withevents' but I can't find how to use it with FileSaveAs event


SaveAs In Excel With VB

I' ve got a problem zith excel '97.

I' ve opened a text file in excel,
made some changes and want to do a save as
from the open file with the same name but
with an other fileformat. The code works but
excel application comes with a msgbox, asking
if I'm sure a want to save the file, because
it already exists. How can I catch that msgbox?
I don't want the user to see this msgbox.
What do I have to do...? Can anybody help me
with this little problem?
This is a piece of my code

appExcel.ActiveWorkbook.SaveAs _
FileName:=TextFile, _
FileFormat:=xlTextWindows, _
CreateBackup:=False, _
Accessmode:=xlExclusive, _
appExcel.ActiveWorkbook.Close _

Excel To Word - SaveAs....

I have a button in excel that opens a word document.

When the word doc is opened i would like to open the SaveAs dialogue box and save the document as the value thats in A:1.

Eg. SaveAs.Filename = <value in A:1>.doc

Any ideas please

Thanks in advance

Saveas Excel With No Macros
I need to save an excel worksheet with all macros disabled so no userforms will pop up and it will be smaller when I copy and paste.I hope it makes sense.

excel 2003 vba

Excel Saveas Webpage
I need to saveas a Web Page a Range of cells but the Range will vary each time (depending upon i). The code won't work I get a runtime 1004 error and I am at a loss to solve, can anyone help?

With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:="C:Documents and SettingsGrahamMy DocumentsRunningSummer Series 2008Summerseries_2008_Ladies.htm", _
Sheet:="Summary Ladies (2)", _
Source:="Range(Cells(1, 1), Cells(i + 4, 30))", _
.Publish (True)
.AutoRepublish = False
End With

Saveas Problem In Excel
Am I doing something wrong or is this just not supported.

This works:
ObjXLBook.SaveAs "c:somefilename.xls"

These don't:
outputfile = "c:somefilename.xls"
ObjXLBook.SaveAs (outputfile)

outputfile = "c:somefilename.xls"
ObjXLBook.SaveAs outputfile

Am I locked into hardcoding the output file name?

Excel SaveAS [* Resolved *]
objXlContacts.ActiveWorkbook.SaveAs strFName, xlNormal, "", "", False, False, xlNoChange, xlLocalSessionChanges , False

I am saving the above Spreadsheet, but it keeps on throwing up the dialog box asking if I want to overwrite the existing file.

Is there anyway to automatically overwrite the existing file?


"A child of five would understand this. Send someone to fetch a child of five." - Groucho Marx

Excel VBA And SaveAs Dialog Box

I want to save some spreadsheets as part of a macro but I want to let the user choose where to store them - in other words I want to use the SaveAs dialog box.

I know how to do this in VB but I can't get it to work in Excel/VBA.

Does anyone have any ideas??



Embeded Excel Obj.saveas ??????
Code Worked

Fails now

Nothing changed

If you hit play again..... after it fails.....

It WORKS! ? !?! !?!?


objsheet.Saveas App.Path & "Saved Characters" & frmchargen.Text21.text

My Stuff
Windows XP
VB6 Pro
Office 2k

Excel, SaveAs Command
Using Excel VBA I'm trying to get allow the user to save the xls to a folder of their choice. One would think, and from what i've read, you could just do this:


But this does not work, all that happens is that it asks me if I want to save over the current file of the same name. It gives me a yes, no and cancel button.

The problem might be that it is an already existant xls file and not a new one but i'm not sure. If any one knows how to fix this problem let me know. it's simple things like this that drive me crazy.


Excel SaveAs Macro
I am trying to figure out a way to have my Excel macro save the workbook/worksheet to a tab delimited file,
but not change the current worksheet file name.

Basically I want to save the tab delimited file, but still have the workbook/worksheet retain its original file name.

I have the macro built that works fine, only I have to close the workbook without saving and re-open it again.


ChDir "K:Data"
Application.DisplayAlerts = False
Worksheets("Sorted").SaveAs Filename:= _
"K:DataMyFile.txt", _
FileFormat:=xlText, ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
ChDir "K:Databases and Logo Lists for Site"
ThisWorkbook.Close savechanges:=False

This is a pretty simple macro, but I want to save the file without making the current workbook have a name change.

Any suggestions?

VB6 SaveAs Excel Workbook
I have 2 problems.

1: When SaveAs an Excel Workbook and the file already exist a MS Office Excel popup ask "do you want to replace it?
if Yes is selected problem, however if No/Cancel a 1004 runtime error "Method 'SaveAS' of object'_Workbook' failed"
occurs. How do I abort the SaveAs when No/Cancel selected?

2: If the Excel Workbook is open when the SaveAs is executed a 1004 runtime error "Cannot Access 'Filename.xls'."
How can close Excel Workbook before the SaveAs and still get the MS Office Excel popup ask "do you want to replace it?

Current code: MyWkBk.SaveAs App.Path & "Reports" & strReportName & ".xls"


Edited by - spaindaniel on 3/3/2008 5:04:01 AM

VB/VBA &amp; Excel SaveAs Problem
I'm fairly new to VB....

Here is my problem:

I have a program that grabs dat files and calculates and parses data and throws it into a txt file. I take that txt file and import it into excel and run a macro to generate charts and pivot tables and all that jazz. At the end of the program I want to save a copy of the excel file and keep the report showing in excel. Everytime I release the object or close my program it closes the excel sheet I was working with, freeing up memory basically. So I thought I would fix this with a saveas but I get the following error: run time error '438' object doesn't support this property or method.

Here is my snippit of code:
Code:Private Sub LoadExcel()
    Set objExcel = CreateObject("Excel.Sheet")
    objExcel.Application.Visible = True
    objExcel.ActiveSheet.Name = "123"
        With objExcel.ActiveSheet.QueryTables.Add( _
        "TEXT;F:GENOAPageSense3_0LogAaronTest123.txt", objExcel.Application.Range("A1"))
        .Name = "AaronTest123"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1)
        .Refresh BackgroundQuery:=False
    End With
    Call Macro2RunAMacro
End Sub
Sub Macro2RunAMacro()
' Macro2RunAMacro Macro
' Macro recorded 7/31/2002 by Joe B. Tester

    objExcel.Application.Run "Book1.xls!CreateDBChart"
    objExcel.ActiveWorkbook.SaveAs FileName:= _
        "C:Documents and SettingsaaaaMy DocumentsTest7312002.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End Sub

I don't know what I did wrong but any suggestions are welcome!

Thanks in advance,


Access 97 SaveAs Excel Export
I have a query and I want to export the results to Excel. In my query I am combining two columns with a chr(10) seperating them. When I export this to Excel it changes the chr(10) to chr(13). Why is this? Does anyone have this problem?

Excel VBA SaveAs Dialog Issue


There is also the 'GetSaveAsFilename' method; look it up in your Excel VB help.

I've implanted this code into VB for getting Save as. The problem i keep getting is that it showed only the window and does NOT save the actual file like i want to get from the save as.

PHP Code:

fileSaveName = Application.GetSaveAsFilename( _
    "\FilelocationNamefile.xls", fileFilter:="Excel Files (*.xls), *.xls")

If fileSaveName <> False Then
    MsgBox "Save as " & fileSaveName 

If anyone can tell me what i do wrong here pls tell me ive been working on this for more then 3 hours!

The problem with the code above is that i have VB-office only so not all the commands will work in my stationary.

Thanks, Axle

Edit by Moderator:
Please post your own threads to ask your own questions, posting links to other threads as required.

Thank you.

Excel VBA SaveAs Dialog Issue
I am trying to use VBA to bring up a SaveAs dialog box with the file name pre-filled in.
I can get the box to show up with the file name, but I can never save the file. I get an error. I tried a few other things that make the error go away but then it just doesn't save it at all.

here is the code.

1 Dim SaveAs1 As FileDialog
2 Dim vrtSelectedItem As Variant
3 Dim SaveFile As String
4 SaveFile = Worksheets("units").Range("myFileName")
5 Set SaveAs1 = Application.FileDialog(msoFileDialogSaveAs)
6 With SaveAs1
7 .InitialFileName = SaveFile
8 End With
10 SaveAs1.AllowMultiSelect = False
11 'SaveAs1.Show
13 If SaveAs1.Show = -1 Then
14 vrtSelectedItem = SaveAs1.SelectedItems(1)
15 ActiveDocument.SaveAsEx vrtSelectedItem, visSaveAsWS
16 End If
I am getting the error at line 15

I am not sure what to do at this point, I've looked every where and most places just describe how to do this automatically but I want the user to choose a folder.

Edit by Moderator:
Please post Exel questions, in the Excel forum.

Please use the [vb][/vb] tags when you post your code. Edit or reply to this post to see how.

Thank you.

Excel - Overriding &"File Already Exists&" Message

In my Excel VBA code I'm doing a SaveAs on a .xls.
However, if the file name already exists in the specified location it asks me if I want to overwrite the existing file.
How can I avoid getting this message and just have it overwrite the existing file?



Why SaveAS Not Work In Microsoft Excel 2000?
Hello, Everyone:
I tried to use Saveas method to save file. It works fine in Excel2002. However, it crashes my problem in Excel in 2002. So, would someone be kind to let me know why and how I can get rid of it?
Thank you very much!

'wkbNewBook.SaveAs xlsfilenameOpen
wkbNewBook.SaveAs FileName:=xlsfilenameOpen

SaveAs Commands And Problems (Excel 2000)
I apologize ahead of time for asking a question that I am certain probably seems stupid to many of you, but I have been trying to figure this out for two days now, and have not been able to find any helpful answers anywhere on the web. Maybe I am searching for the wrong keywords, or maybe I am just totally ignorant. The major problem I have found is that, whenever I find a possible solution, the author of the script has not explained any of the elements of the script, so I don't know how to change things so that they fit my particular situation. Here is what I would like to do:

I want the user to click on a radio button that will automatically save the file under a new filename. I have that radio button associated with a macro.

Here is what I want the macro to accomplish:

I want to collect the contents of a cell called "SvcAdd10" and the contents of a cell called "SvcAdd15", and store both of those values as variables. I then want to set the filename to include both of those variables. For purposes of illustration, let's say that the values in those cells are "13059" (SvcAdd10) and "Rumblebuffin Place" (SvcAdd15). The filename that I want to save this workbook as would be, in this case, "Rumblebuffin Place-13059 - I&R Boiler.xls". I also want the macro to search for a directory within a network drive that contains the values of SvcAdd10 and SvcAdd15. If no such directory exists, I want to create one.

So, the macro should perform the following actions in the following order (if possible):

Store the contents of SvcAdd10 as a variable called "FileName1"

Store the contents of SvcAdd15 as a variable called "FileName2"

Store the filename as a variable called "strFileName" (I saw a little bit of help on this part of the script at, but the person who wrote the script did not tell me what "Employee_form" was, or what "l_name" or "f_name" was. I assumed that Employee_form was either the name of the workbook, or the worksheet, but I got an error when I replaced it with my own names. I also assumed that "l_name" and "f_name" were the names of specific cells, but I get "Compile Error: Method or data member not found" when I write it like this: "FileName1 = ThisWorkbook.SvcAdd10").

Change the drive to a network drive (I have tried doing this using the method that "Help" explains, but I always get a (Compile error: Expected: expression) when I set it up like this: "ChDrive (\sesmainC:)").

Change the directory to the appropriate directory on that network drive.

Search for a directory that contains the values of SvcAdd10 and SvcAdd15 in any order.

Change to that directory, if it exists. If it does not exist, I want to create a directory that would be called "SvcAdd15-SvcAdd10" (from my earlier illustration, the name of this directory would "Rumblebuffin Place-13059". Then, I want to change to that directory.

Save the file in that directory, using the filename that we stored as "strFileName".

Again, I apologize if this seems like a simple thing to ask. As I said, I have searched the Internet for the last two days, looking for things like "variable in filename visual basic for excel", "store contents of cell as variable visual basic for excel", etc., etc., etc.

If you do not feel like taking the time to write a script that would do what I ask, can you please at least point me toward a site (one that I don't have to pay for) that contains help on these subjects. I am willing to do research, if I know where to look. Unfortunately, though, as I said, I have not had much luck in my research, thusfar.

Thank you in advance for your help. I have read quite a few of the posts in this forum, and I see that there are quite a few people here that know how to use VBA for Excel very proficiently. I am yet a beginner, simply trying to learn how to accomplish a few simple tasks, and I thank you for your time, even if all you do is read my post.

Excel Changing To American Date Format On Saveas Csv!??
I have a problem that has really thrown a spanner in the works so to say.
I have an excel sheet that is automatically generated from a set of data imported from a text file. This sheet is then, in turn, saved as a csv file to send to a partner. The problem is this: even though excel is displaying the dates as european date format on the sheet, when I save as a csv it saves them all as american date format!

Does anyone know a simple workaround short of writing a process to re-open the file and manually swap all the dates?

Any help MUCH appreciated!

SaveAs CSV File
The following code does some modification to an excel file and then saves as a CSV. The problem I have is that the resulting CSV file has a lot of trailing commas as though there was data in those cells...and ideas how to get rid of the commas?

Dim i As Integer
Dim numrows As Integer

Set myRange = Range(Range("A2"), Range("A65536").End(xlUp))
numrows = myRange.Cells.Count

For i = 16 To numrows + 1
Range("A" & i).Select
Application.CutCopyMode = False
If ActiveCell.Value > 9 Then
ActiveCell.FormulaR1C1 = "L501-0" & ActiveCell.Value
End If
If ActiveCell.Value < 10 Then
ActiveCell.FormulaR1C1 = "L501-00" & ActiveCell.Value
End If
Next i

Selection.delete Shift:=xlUp

ActiveWorkbook.SaveAs Filename:="I:PrepInsiRNA.req", FileFormat:=xlCSV

Thanks to anyone who helps!


Can I Preset The SaveAs File Name?
This is something I probably should know, but how would I go about displaying a default for the file name displayed when I do MyDialog.ShowSave?

Macro File Saveas
Anyone know why this macro won't work?

Sub SaveDocHTML()

ActiveDocument.SaveAs , Application.FileConverters("HTML Document").SaveFormat
MsgBox "Saved in HTML format successful."

End Sub

- OR -

Sub SaveDocHTML()

ActiveDocument.SaveAs , Application.FileConverters(6).SaveFormat
MsgBox "Saved in HTML format successful."

End Sub

the broken line is really on one line in the code window.

Thanks for your assistance yet again.


File Open Split Saveas
Hello, I hope make program to open file; split text data; and saveas,
A) first open file "a.txt"
B) split file ; and change split
C) save as file "b.txt"

but my TXT file is very very Large, 200mb, around ~5,000,000 lines

but I do not understand 3 steps to 1 step, can you help me!

many thanks!

!!||||AUSERNAME @@@@#### ZTIMELOGAAA #####
!!||||BUSERNAME @@@@#### YTIMELOGAAA #####
!!||||CUSERNAME @@@@#### XTIMELOGAAA #####

Dim sp2() As String
Dim resplit As String

sp2 = Split(Str, "@@@@####")
resplit = sp2(0) + "^^6^^" + sp2(1)

Text3.Text = Text3.Text + resplit + vbCrLf

Private Sub Command1_Click()
'open file
Dim FileNameWithPath As String
Dim Str As String

Dim F As Integer
F = FreeFile

FileNameWithPath = "M:a.txt"

Open FileNameWithPath For Input As #F
While Not EOF(F)
Line Input #F, Str

'RichTextBox1.Text = RichTextBox1.Text + Str + vbCrLf

Close #F

End Sub

Private Sub Command3_Click()
'save as file
Dim f As Integer
Dim FileNameWithPath As String
FileNameWithPath = "M:.txt"

f = FreeFile

Open FileNameWithPath For Output As #f
Print #f, Text2.Text
Close #f

End Sub

Using A Bookmark As A SaveAs File Name – Word VBA
In an Excel worksheet it is possible to use the text in a cell for the SaveAs file name. For example:

ThisWorkbook.SaveAs FileName:=Worksheets("Sheet1").Range("A1").Value
Dim myFile As String
myFile = Range("A1")
ActiveWorkbook.SaveAs _
("C:Data" & myFile & ".xls")
End Sub

Is it possible in a Word document to use a bookmarked word as a SaveAs file name. If not, is there any other way to reference a word to use as the SaveAs file name?

Your help is much appreciated.

Copyright © 2005-08, All rights reserved