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




FileDialog SaveAs


This code dumps a table from Access 2002 to Excel but requires me to specify the destination file name. I'm looking for syntax for a typical SaveAs dialog that allows the path and file name to be user specified.

Suggestions?


Code:

Function ExportToExcel()
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"MasterData", vrtSelectedItem & "Master.XLS", True
Next vrtSelectedItem
End If
End With
Set fd = Nothing
End Function




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Filedialog Saveas Filter Type?
Hi everybody!

I wrote a macro wich converts an excel sheet to a postscript page, and this starts with a filedialog type 'save as...' but when I try to set the filter type to "*.ps" vba says that's not allowed? Someone knows how to set the filter of a 'save as...' dialog?

Thanx!

Open A Filedialog
Hello. Can one of these(please refer to attatchment) be opened with vb code? And if so how? I want the user to pick a certain file and when they click open, on the filedialog, it puts the path into a text box. I need to know if there is a command I can use to open windows default filedialog box and obtain a string from it. Thanks.

And while we are on the subject how do I populate some sort of list with a directory tree of a computers drive? Thats just in case I want to build a filedialog form in the future.


P.S. I really did do a search for it here first...

FileDialog Box Won't Show
I'm working in VBA 6.0 (Version 9969) in Access (9.0.6926, SP-3). I should be able to use a FileDialog object, which is a - you guessed it - File Dialog box. I have a reference set in the references dialog box to mso9.dll. This is the dll for Microsoft Office 9. This should make the FileDialog available to the program. The Object Explorer shows the FileDialog as a member of the Office object. But the compiler can't see it ("Method or Data Member Not Found"), and the AutoComplete doesn't generate it. This code worked on another machine in another office across town, but it's not working here. I can't figure out what the difference is. I have also tried swapping in mso.dll, which is the dll for Office 11 (can't find Office 10 around here)

What am I missing? A big clue might be that I remember VB asking me if I wanted to install something, I said yes, and afterwards I could use the FileDialog. But I don't remember how I got there.

Any ideas, questions?

Problems With FileDialog In Access
Hello!

I have used this code in Excel, to open File Dialog and define the chosen file as Valgt Fil



vba code:--------------------------------------------------------------------------------
Sub cmdChooseFile_click()

ChDir "c:"

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
ValgtFil = vrtSelectedItem
Next vrtSelectedItem
Else
End If
End With

End Sub

--------------------------------------------------------------------------------


Can anyone tell me why this won't work from an access form?

tnx

FileDialog Small Problem
I want to export a module to a text file. The user can choose the path and filename of the text file. I am using a filedialog to let the user choose path and filename. Here is the code:

Code:
Private Sub cmdBrowse_Click()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Filters.Add "TXT-bestanden", "*.txt", 1

Dim vrtSelectedItem As Variant
If fd.Show = -1 Then
For Each vrtSelectedItem In fd.SelectedItems
txtPath.Text = vrtSelectedItem
Next vrtSelectedItem
End If
End Sub
This works when the user selects a text file that already exists. But when the user types a filename himself and clicks 'OK', nothing happens.
How should I solve this?

Access FileDialog Object
I am using MS Access 2002 and use the new FileDialog object to allow my users to select a file from within a form. This works fine if the user's machine has Access installed on it. If I have to install the Access 2000/2002 run-time if they don't have it though, the application crashes when the user tries to open up the FileDialog object.

Does anyone know of a way to include the Office 10.0 objects module with an installation? Or whichever module is needed to use the FileDialog object? I'm currently using InstallShield 4.0 Express. Thanks!

FileDialog Not Working In MSAccess VBA
Hi all,

I'm trying to get a File Open Dialog box in VBA (MS Access) so I can get the path to the folder and use within a text field.

I have the following code supplied by the MS Access VBA Help file to open a dialog box:

Dim dlgOpen As FileDialog

Set dlgOpen = Application.FileDialog( _
FileDialogType:=msoFileDialogOpen)

With dlgOpen
.AllowMultiSelect = False
.Show
End With

But all I get is a Compile Error: User Defined Type not Defined

I've tried everything but have no idea why it's not working. Is this something I'll need to activate (object support) before this object becomes available? Or is it something more complex? When I type in Application. I don't get a list of properties underneath which makes me wonder if this object has been activated?

I'm fairly new to VBA in MS Access so any help would be very much appreciated.

Thanx

VBA: FileDialog(msoFileDialogOpen) -- File Name
I would like to get the file name from the File Open Dialog Box I am calling via VBA code so that I can select and External Files Path for a macro I am writing.

This macro will be used in multiple files and needs to refer to file locations that can be anywhere on the hard drive.

I've got my code laid out thusly

Code:


Dim objExcel As Excel.Application
Dim ExcelFile As Variant
Set objExcel = New Excel.Application
objExcel.Visible = False
Application.FileDialog(msoFileDialogOpen).Show
objExcel.Workbooks.Open (ExcelFile)



I then can go so far as to add code in to do what I thought would take the name from the code but it's taking the value of the FileDialog.Show being -1. I need the name/path to the file to be made ExcelFile so that I can open that workbook and pull data from it for the file that is being developed.

Thank you in advance

Owyn999

Application.FileDialog(msoFileDialogSaveAs)
i get error "Object doesn't support this action " on this line:
With Application.FileDialog(msoFileDialogSaveAs)

msoFileDialogOpen and others work, but SaveAs doesn't
Anyone know a reason ?



Edited by - totenhose on 5/21/2004 3:40:17 AM

Showing Only Selected Files In FileDialog?
hi,

is there a way using FileDialog to display
only a list of known files? if not, is it
possible to implement something similar?
the items in the list have to be recognized
as file objects as FileDialog, not just
plain data.

thanks in advance

eq

SaveAs With SaveAs Dialog?
Hi all :)

I found this on MSDN, and is *almost* what I need - the difference being that I'd like to bring up the Save As dialog, so that the user can choose the filename and location to save the sheet.


Code:
Sub SaveSingleSheet()

Worksheets("Sheet1").SaveAs filename:="test.xls", _
FileFormat:=xlExcel4

End Sub

To be honest, I have no idea what to try - I've tried just: Worksheets("Sheet1").SaveAs, but to no avail. I haven't found this in a search of this forum, and as I said, that's all I could find on MSDN.

A poke and prod in the right direction would be much appreciated, thanyou ;)

FileDialog "Up One Level" Icon Inactive
I am using the following code in a Powerpoint macro to open a
file dialog box.

Set dlgOpen = Application.FileDialog(msoFileDialogOpen)

With dlgOpen
.Filters.Clear
.Filters.Add "All Images", "*.bmp; *.gif; *.jpg; *.jpeg", 1
.AllowMultiSelect = True
.InitialView = msoFileDialogViewPreview
.Show
End With

However, I am unable to get the "Up One Level" navigation icon to activate when the dialog box opens. This is true of all icons in that
toolbar and in the "Look In:" pane. I have tried the different dialog
types and have looked at other properties.

I am open to suggestions.

SaveAs
I was wondering if anyone could help me come up with saveas code for my vbapp. what i have now is

Code:
With CommonDialog1 'open a with statement as there is a lot of code
.DialogTitle = "Save" 'sets the dialog title
.InitDir = "C:" 'sets the initial directory
.Filter = "Text Files (*.txt)|*.txt|All Files (*.*)|*.*" 'sets the file types
.ShowSave 'show the dialogEnd With
End With
.
I would like to save the information to a text file. and i need it to be able to create a file with the name typed in in the commondialog if the file didn't already exist.

SaveAS
I have created an application that takes data from MSSQL database and creates a report in Excel. When it comes time to save the Excel file, I have the common dialog come up. Everything works fine, but I have this annoying problem.

When file name is the same as an existing file, the common dialog flag is set to handle the overwrite option and the message box pop up asking to overwrite. If the user click "yes" then my application tries save the file and the application pops up another overwrite message box. How can I prevent this second message box from popping up?

Note: I want to keep common dialog overwrite checking, since I do not have to write code to make it loop when the user clicks on "no", unlike the application message box.

Also, how can I save the file as a read-only file?

SaveAs Help
Beginner talking here. I'm trying to save a file using the SaveAs method in a macro. Here is where I'm at right now and I know it don''t look right.


fname = Application.GetSaveAsFilename:
If fname = False Then GoTo line10 Else
ActiveWorkbook.saveas Filename:=fname
line10:
End Sub

The application/macro runs well when 'fname' is a new file name. However if the filename is the same as a previous one, then I loose control of the macro. How can this saveas method be modified? Thanks for help.

G

Need Help With A SaveAs
Ok all, I'm really dumb when it comes to Macros... Can anyone help with this easy question?

I have a workbook called Final.xls and Attached to this workbook are several very long macros the will auto run when the workbook is opened. What I would like to do is force the person to save the Final.xls to another name that the user chooses at the very end of the macro execution.

Here is my problem... First, how do I get a SaveAs window to pop up at the end of the process? Second, I want the cancel button on the SaveAs window to be disabled so that the user is forced to change the name. And third (and most importantly), I want the workbook to SaveAs another name WITHOUT the macros in Final.xls coming along for the ride.

Any thoughts? Thanks in advance for the help! I've found a lot of great help here from you all in the past,

SaveAs
I use the following code to save selected sheets from my workbook to a new workbook.
Everything works OK, menue, filename etc, but it does not save the file.

Some ideas.


Code:
Sheets(Array("BOMA", "Rapport2", "Rapport3")).Copy

Dim strFilt As String, strFileName As String, strSaveAs As String
strFilt = "Microsoft Excel File (*.xls),*.xls"
strFileName = ThisWorkbook.Sheets("Rapport2").Cells(2, 11).Value
strSaveAs = Application.GetSaveAsFilename(strFileName, strFilt)

SaveAs
I am attempting to save an Excel file through code. The name of the file is static, so I am wondering if there is a way to by-pass the "This file already exists..." dialog box and just have the program automatically save the file. Thanks in advance!

Saveas Box
how can i use the saveas box?

Using 'SaveAs'
I'm using this line of code in my project to save an excel file after I've updated it.

'Save Uptime.xls
   Call exlBook.Worksheets(1).SaveAs("C:Uptimeuptime.xls")

Problem is, I'm overwriting an existing file and I get the infamous "Do you want to overwrite?" prompt where I have to answer yes or no. Is there some way of making my file overwrite without having to tell it to?

Thanks



Edited by - jeffeason71 on 12/18/2003 9:32:44 AM

SaveAs Path
Code:
'delete all in database
Dim filename As String
Dim Conn As ADODB.Connection
' Open a connection.
conn = New ADODB.Connection
Conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:checkerfilesclaims.mdb;Persist Security Info=False"

conn.Open()

Conn.Execute("DELETE FROM Table1", , )

'show openFileDialog
Dim fsave As New OpenFileDialog
fsave.ShowDialog()

'sort excel sheet
Dim xlapp As New Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

xlapp = New Excel.Application
xlbook = xlapp.Workbooks.Open(fsave.FileName)
xlsheet = xlapp.Workbooks.Item(1).Worksheets.Item(1)
xlapp.Visible = False

xlsheet.Rows(1).insert()
xlsheet.Cells(1, 1) = "Equiptment"
xlsheet.Cells(1, 2) = "depot"
xlsheet.Cells(1, 3) = "checker"
xlsheet.Cells(1, 4) = "date"
xlsheet.Cells(1, 5) = "combi"
xlsheet.Cells(1, 6) = "store"
xlsheet.Cells(1, 7) = "item"
xlsheet.Cells(1, 8) = "scanned"
xlsheet.Cells(1, 9) = "ordered"
xlsheet.Name = "sheet1"
xlapp.ActiveWorkbook.SaveAs("c:checkerfiles& fsave.FileName &", Excel.XlFileFormat.xlExcel9795, )
'clean up
xlbook.Close(SaveChanges:=False)
xlapp.Quit()

xlapp = Nothing
xlbook = Nothing
xlsheet = Nothing
GC.Collect()

'dump excel file in to database
Dim cSource As String
cSource = "c:checkerfiles& fsave.FileName &"

Dim oCon As ADODB.Connection
oCon = New ADODB.Connection
Dim cSQL As String

With oCon
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cSource & ";Extended Properties=Excel 8.0"
.Open()

cSQL = "INSERT INTO Table1([Equiptment],[Depot],[Checker],[Date],[Combi],[Store]," & _
"[Item],[Scanned],[Ordered]) IN 'c:checkerfilesclaims.mdb 'SELECT * FROM [Sheet1$]"
.Execute(cSQL)
End With
End Sub

How can i use the file name chosen in the openfile dialog again to save as my new files name. The reason i am doing this is the files being opened are excel csv format and i want them in 95-97 workbook format before they go to my database. all works perfectly except this line

Code:
xlapp.ActiveWorkbook.SaveAs("c:checkerfiles& fsave.FileName &", Excel.XlFileFormat.xlExcel9795, )
I have tried several variations of the save string with the "`s in different places and without the &`s but just get a new file called & fsave.FileName &. If i open a file called 123 i want the new one saved called 123 also .

How To Tell Word The SaveAs Name From VB
Hello All,

Another interesting question I hope someone can answer. Right now I create a word document from a template and pump in0 specific information from VB. The problem for me lies in the SaveAs - I want to display a certain name in the Word SaveAs dialog box and not the usual first line of text in the document. I tried changing the Document.Name and Document.Fullname and even the Document.BuiltInDocumentProperties(wdPropertyTitle) properties. It still defaults to the first line of text in the document which is not what I want the user to have. I don't want to have to actually use the Document.SaveAs feature, then make the user change the name and kill it if he wants something else.

Any suggestions?

Thanks.

Odd SaveAs Behavior
I'm trying to do a SaveAs on a document template. It saves the document as "FileName.doc" and the file looks like a regular .doc file after it's created. The problem I'm having is if I want to make changes to that file and save it manually the File Type option is greyed out and is set to Document Template. So what I have to do is save it as a template, open the template and resave it as a .doc file. I've tried to use the FileFormat:=Word.wdFormatDocument and the whole SaveAs process gets skipped over and the file is not saved at all. I know I'm doing something wrong. Any suggestions?

Stumped By SaveAs
The Word macro recorder is driving me crazy. What seems to be a simple macro turns out, once again, to be rocket science (at least for someone with my limited vba skills).

I'm trying to create a macro that:

1. Finds a selection of text based on the font formatting - no problem
2. Activates the SaveAs command and uses the selected text as the new filename - PROBLEM

When I record the keystrokes, the macro keeps using the first piece of selected text for all subsequent SaveAs operations, and ignores the selected/copied text.

Here's what I've got so far. Any assistance will be much appreciated.

Code:
Sub ReName()

Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Font.Size = 14
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
ChangeFileOpenDirectory _
"E:My Documents"
ActiveDocument.SaveAs FileName:="Test", FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
ActiveDocument.Close
End Sub

AJ

Using SaveAs To Save As A Different Name
Hi everyone, thank you so much for helping me with my problems. Marcus has helped me work out my checkbox problems and I have another question. Here goes...

I want my document to be saved as a new document when my user click on a button. I would like store the user name in a variable and use the variable as part of the file name. If the user save the game more than once I don't want the original file to be overwritten with the same name. I would like to have the name appended with a number or something.

This is the only code that I found would work:

ActiveDocument.SaveAs FileName:="C:Documents and SettingsAdministratorDesktopTemp.doc"

I tried adding a number to the file name like this:

ActiveDocument.SaveAs FileName:="C:Documents and SettingsAdministratorDesktop"+intFileCount+"Temp.doc"

but it won't work. Please help

SaveAs Formatting
G'day

I have written a user friendly excel spreadsheet that creates a formatted data file for a Power Analysis software. I want create a routine that automatically saves the text file (.prn format) when datasheet is constructed (by a button lik) on a worksheet. But, I want to ask the user to specify the filename and locatation and give the option of browsing (like the SaveAs format of windows applications). Can anyone help me.

Also is there anyway to close the XXX.prn spreadsheet that overwrites the excel formatted spreadsheet, return to the .xls spreadsheet for further use and open the txt file in wordpad.

Cheers Guys, I am at a loss

Activeworkbook.SaveAs
I have created an addin named Test.xla, which has a class module to define the WorkbookAfterSave event:

Public WithEvents appevent As Application

Private Sub appevent_WorkbookBeforeSave(ByVal wb As Excel.Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim answer as boolean

'Prompt user to choose Yes/No/Cancel if they want to attach a reference to this addin
answer = promptUser

if answer then
wb.VBProject.References.AddFromFile ThisWorkbook.FullName
end if

End Sub


Now I open a new workbook in Excel, create a macro:

Public sub Test()
Activeworkbook.SaveAs "C: emp est.xls"
End Sub


When this macro is run, the user is prompted with the choice.
It doesn't matter whether Yes or No is chosen, the workbook being saved is the addin itself, and not the actual Excel workbook.
Somehow, Excel is treating the addin as the ActiveWorkbook and save it.

Does anyone know why?

SaveAs Error
this will probably be a quick problem to fix
SaveAs method in this case is not working, and Im not sure why. Every time I run the macro excel gives error message

"'06.csv' cannot be accessed. The file may be read only, or you may be trying to access a read-only location. Or, the server the document is stored on may not be responding"

The file isnt read only, and I have no idea how to fix it. Here's the code, and the SaveAs problem is at the bottom. Thanks everyone

Code:
Function GetLastRow(SheetID) As Integer

Dim LastRow As Integer

If Application.WorksheetFunction.CountA(Worksheets(SheetID).Cells) = 0 Then
LastRow = 1
Else
LastRow = Worksheets(SheetID).UsedRange.Rows.Count + Worksheets(SheetID).UsedRange.Row

While Application.WorksheetFunction.CountA(Worksheets(SheetID).Rows(LastRow)) = 0
LastRow = LastRow - 1
Wend
End If

GetLastRow = LastRow

End Function

Sub AcmWalletImport()

Dim i As Integer
LastRow = GetLastRow(1)
Dim PODate As String


'trim space in all cells

For Each rng In Range("A1:AZ" & LastRow)
rng.Value = Trim(rng.Value)
Next


'Erase stupid headers


Cells(1, 1).Select
If Cells(1, 1).Value = "LASTNAME" Then
ActiveCell.EntireRow.Delete xlUp

'take care of 4 digit zip code

Range("H1:H" & LastRow).Select
Selection.NumberFormat = "@"
For i = 1 To LastRow
If Len(Range("H" & i)) = 4 And Range("AW" & i).Value = 1 Then
Range("H" & i).Value = "0" & Range("H" & i)
End If
Next i
Range("AL1:AL" & LastRow).Select
Selection.NumberFormat = "@"
For i = 1 To LastRow
If Len(Range("AL" & i)) = 4 And Range("AW" & i).Value = 1 Then
Range("AL" & i).Value = "0" & Range("AL" & i)
End If
Next i

'format date from yyyymmdd to mm/dd/yy

Range("S1:S" & LastRow).NumberFormat = "mm/dd/yy"

'Create country code

For i = 1 To LastRow
If Range("AW" & i).Value = 73 Then
Range("I" & i).Value = "United Kingdom"
ElseIf Range("AW" & i).Value = 65 Then
Range("I" & i).Value = "France"
ElseIf Range("AW" & i).Value = 19 Then
Range("I" & i).Value = "Belgium"
End If
Next i

ActiveWorkbook.SaveAs "C:Documents and SettingsHP_AdministratorDesktopAcmWallet" & Format(Date, "mm/dd/yy") & ".csv"


Else: MsgBox "You're trying to delete a record, not the headers" & vbNewLine & _
"Meaning, you probably already pushed the button to format this document, but, I still loves you.", vbCritical


End If

End Sub

Saveas Question
Hi All,

I am trying to use a microsoft word (v. 2002) macro to save any document I am in to a specific directory on my hard drive. This is a option I have to do lots at work normally in a directory far into the hard drive so manually getting there takes ages. I have created a macro using the record macro function and have created the basic macro but the problem I have is that it always names the file the same filename. I need it to name it the name of the current file I am in.

If anyone can suggest what I need to change I would be very greatful - if I could have it by tomorrow I would be over the moon!

For instance in example below I can use this in any document but it will always name the file 'Doc 3'. How do I make it automatically save it as the current file name?

Sub Reg1()
'
' Reg1 Macro
' Macro recorded 11/11/2007 by martin
'
ChangeFileOpenDirectory "C:Documents and SettingsmartinMy Documents"
ActiveDocument.SaveAs FileName:="Doc3.doc", FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False
End Sub

I use microsoft word v 2002. Many thanks for your help!

Martin.

Simple SaveAs Help
Hello,

I would like to use my macro to prompt users to save a worksheet, the directory where these sheets belongs has a fairly long path. The code I have now is

Application.FileDialog(msoFileDialogSaveAs).Show

and this brings up the familiar "save as" dialog box. I would like to know what I can add that will allow me to specify the directory where the file is saved.

Thanks,

Matt

Problem With SaveAs .txt
I am trying to overcome a problem when saving an Excel file as a Text(MSDOS) file:

I have a VB script which saves an Excel file in text format. It works fine, except that Excel puts quotation marks around any text string which contains commas. If I manually save the very same Excel file using the File-->SaveAs-->Text(MSDOS) method, no quotation marks are added.

In this particular application, I will always have some text strings which contain commas, so I can't just replace the commas with another character or do some kind of work-around. I need to actually find out how to automate the SaveAs to work the same way as it does when executed manually.

Any help appreciated, Thanks!

R. Thomson
Copenhagen, Denmark

SaveAs And No Option
Guys,

Maybe I am missing someting completely obvious here, but I am puzzled by what happens when you are using

myWorkbook.SaveAs fileSaveName

when an existing fileSaveName is provided. I can see why the dialogbox asking if you want to overwrite pops up, but how can you handle the case where a user chooses "no", without going through error trapping? And why supply that option, if you cannot check whether the user chose it or not?

Right now it looks to me as if the "no" button simply triggers an error, and there is nothing you can do about it except handle the error. Another post suggested to use saveas only when you were sure the file name was OK, but then why supply the "no" option???

Any light appreciated!

Mathias

Excel.SaveAs
Am having problem with saveas syntax.

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

thanx

Error In SaveAS
I use this code for saving selected excel sheets into one new workbook.
My problem is that i have to click twice on either OK or Cancel to get it executed.
What's wrong here?


Code:
Worksheets("Lineconfig").Range("A3:BS" & r + 2).Copy
ActiveCell.PasteSpecial xlPasteAllExceptBorders
Range(ActiveCell, ActiveCell.Offset(13, 0)).RowHeight = 12.75

Sheets(Array("Rapport2", "BOMB", "BOMA", "Rapport3")).Copy

Dim strFilt As String, strFileName As String, strSaveAs As String
strFilt = "Microsoft Excel File (*.xls),*.xls"
strFileName = ThisWorkbook.Sheets("Rapport2").Cells(2, 11).Value & ".xls"
strSaveAs = Application.GetSaveAsFilename(strFileName, strFilt)
Application.Dialogs(xlDialogSaveAs).Show strFileName 'Show Suggested name

Saveas With Variables
How can I read variable values into the saveas command so that way when a user clicks the saveas, it will pull the last name + firstname as the filename. This is what I have from the macro recorder:


Code:
Dim a As String
Dim b As String

a = Employee_form.l_name
b = Employee_form.f_name
'
ChDir "c:"
ActiveWorkbook.SaveAs Filename:="c:""a""_""b" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
I know, as always, I am not doing it correctly. Especially when I get a RTE by VBA. . So if anyone would help me how to call a variable from within a string I would be greatful. Thanks for all your help guys.

Language Changes Upon SaveAs
When I use Workbook.saveas, the language in my workbook changes!!

I am from Norway, so my computer and software has Norwegian elements in them, bu my Excel is English. But when I use the activeworkbook.saveas, all my formulas are changed to Norwegain (so IF becomes HVIS, SUM becomes SUMMER e.g.). This messes up any further work in the workbook, since my formulas are later inserted in English.

However, if I close the newly saved workbook, and reopens it, then the formulas are back to English again. I’m confused here.

This “error” does no happen I use the Save As from the File menu. I tried recording a macro while doing a manual Save As, but I got the same result when running this code directly in a macro.

Does anybody know what’s going on, and how to avoid it?

-Terry

Saveas Problem
hey guys, i am new here....and i am also not too good with explaining good in the english language, so sorry if i get u confused.

i am working on a macro and now i am stuck. What the macro does is change the values on an entire table i created in excel. Then i managed to automatically popup the save as window. The problem is that after i type the filename, if the filename already exists in the directory, then excel will tell me that the filename already exists. I do want that aleart to popup but it only pops up 1 time. If for example i type another filename that already exists, then it gives me and error.
this is the part of the code where i am stuck. Please try it to see exactly what i mean.

b:
Set newbook = ActiveWorkbook
Do
fname = Application.GetSaveAsFilename
Loop Until fname <> False


If Right$(fname, 3) <> "xls" Then fname = fname & "xls"

On Error GoTo b
newbook.SaveAs Filename:=fname

i also created a msgbox that gives the option to confirm overwrite, but the excel aleart window still pops up and still it only happens once, the second time it gives me an error.
All i need to do is to have either the excel aleart to pop up everytime or to make a msgbox that says "the file already exists" "conferm overwrite" yes/no, if yes then save and if no then go back to b:

Please help me.
Thanks

SaveAs But Don't Overwrite
HELP! I am new to this...very new. Anyway. Below is a simple code for a SAVEAS macro. How do I tell it NOT to overwrite existing files named the same?

Sub docsave()
var1 = "F:PRIVATE"
docsave1 = InputBox("Enter name of file", "SAO Word Processing", var1)
If docsave1 = "f:private\" Then docsave1 = ""
If docsave1 &gt; "" Then ActiveDocument.SaveAs docsave1
End Sub

Saveas In A Vb Web Browser
Saveas in a vb web browser
Hello
I need a code to saveas without running the saveas dialog box.
Here is the code in the web browser running from a internet made to use within visual basic.
Where to find the code to saveas htm in the vb web browser.
Private Sub mnuFileSavePage_Click()
WebBrowser1.SetFocus
On Error Resume Next
WebBrowser1.ExecWB OLECMDID_SAVE, OLECMDEXECOPT_DODEFAULT
End Sub

How can i bypass the Saveas Dialog and save directly to my drive

HELP ME With SaveAs Command
Hi everybody

I need some help.

Working on a Save Button on a spreadsheet where I want to save the workbook as a unique filename each time I hit the save button.

The filename would be a concatenation of specific fields on the worksheet

for example:
first i want to save the worksheet as
"march 23 50455 BillGates.xls"

then when i change the fields, and hit save again, it will save it as
"march 25 65867 SteveJobs.xls"

how do i make the filename to save based upon field data?
this is what i had, but it is not working

ActiveWorkbook.SaveAs Filename:="C:DCABillingRange("a177").Value"




CAN ANYONE HELP ME??



Thanks in advance....

Datareport SaveAs
hi guys! is it possible to create a datareport that can be save as another file or so that it can be save into flopy disk and print individually?

Datagrid/SaveAs
I want to save and print my datagrid, I know I have to add the mscommon dialog control, But I not sure how to code the saveAs and print commands for this.

SaveAs Is Failing
I have a tuned Excel workbook that I am trying to just save as a new name and serveral statements are getting the "dreaded" 1004 error. The particular line that is failing is in ComboBox statements within an If statement on the Else leg:

Code:
Private Sub ComboBox8_Change()
If ComboBox8.Value <> "Extranet Utility Web" Then
Range("C52").Value = ComboBox8.Value
Rows("52:53").EntireRow.Hidden = False
Range("C53").Select
Else
Range("C52").Value = ComboBox8.Value
Rows("52:53").EntireRow.Hidden = True
Range("C53").Value = 0
Range("B3").Select
End If
End Sub


The 1004 error occurs on the
Code:
Range("B3").Select


Also, I am receiving the 1004 on the following code:

Code:
ActiveSheet.Unprotect
Worksheets("Reset Values").Range("B2").Copy
ActiveSheet.Paste Destination:=Worksheets("Services").Range("B2")


The error is
Quote:




The cell or chart you are trying to change is protected and therefore read-only.




but as you can see there is an unprotect statement that preceeds it.

Any ideas?

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

Rows("1:15").Select
Selection.delete Shift:=xlUp

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


Thanks to anyone who helps!

Shannon

If SaveAs Is Canceled
Hello,

Kind of newbie question:


In the SaveAs Dialog box,
What's the code to check if the User clicks on the CANCEL button?

ActiveWorkbook.SaveAs FileName:=varFileName, FileFormat:=xlWorkbookNormal


Thanks,
CJ

Clipboard To Saveas?
I have some code that copies the contents of the clipboard to a file:

VB Code:
Call SaveScreen("C:ss.bmp")

I have also got this code to open a saveas dialogbox:

VB Code:
Dim sFileName As String        With CommonDialog1        .Flags = cdlOFNOverwritePrompt + cdlOFNExplorer        .Filter = "Bitmap files (*.BMP)|*.BMP"        .ShowSave        sFileName = .filename    End With        If sFileName <> "" Then        Open sFileName For Output As #1            Print #1, ("C:ss.bmp")        Close #1    End If

I would like to combine the 2 peices of code so that you get to choose where to save the bmp.
I've tried "Print #1, ("C:ss.bmp")" but that doesn't work,
anyone have any suggestions?
Thanks in advance,

SaveAS Save
Dears
i have proplem at VB can u helo me i made programe to load picture at form and draw at picture ExText ,circle,box)
this thinge are made at picture at Runtime
but when is store picture with Fil------>>Safe or file----->Save AS
i see that Vb can't store this thiges at picture but he store picture only
** Vb dddn't store Changes at picture but Vb store picture only without ang cchanges and the code is:

***************
dim openfile
Private Sub save_Click()

If OpenFile <> "" Then
SavePicture Picture1, CommonDialog1.FileName
End If
End sub
-------------------------------------------------------------------
Private Sub saveas_Click()
CommonDialog1.ShowSave
CommonDialog1.DefaultExt = "jpg"
If OpenFile = "" Then Exit Sub
SavePicture Form1.Picture1, CommonDialog1.FileName
OpenFile = CommonDialog1.FileName

End Sub

So can u help me please

Excel SaveAs
Hi

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

thanks

Cannot Work Saveas
I'm using :

Excel.application.Workbooks(1).SaveAs "C:Myfile"

Can anyone please tell me how to force the save? i.e. I get the message "a file named the same already exists - replace?". If I choose anything but the yes, an error's generated, can I force a yes here behind the scenes so no message appears?

Cheers!

Copyright © 2005-08 www.BigResource.com, All rights reserved