Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Application.Dialogs(xlDialogOpen).Show

Hi All,

I am using Application.Dialogs(xlDialogOpen).Show but it goes straight to the Import Wizard, I have tried to supress this using display alerts but to no effect. Can anybody help me supress the wizard? The file does not need anything done, just to click on Finish and the jobs a good one.

Many thanks in advance.


Post your answer or comment

comments powered by Disqus
Hi,

Does anyone has this problem before:

Application.Dialogs(xlDialogOpen).Show txtPath.Text ... hang, if txPath.Text is pointing to a mapped network drive (e.g. F: or Z

Excel version 2010 VBA

Thanks in advance

Hi,

I'm experiencing weird problems with this 'apparently' basic piece of code:
(where 'Sheet1' is the sheet CodeName)

Sub FindInSht1()
Sheet1.Activate
Columns(2).Select
Application.Dialogs(xlDialogFormulaFind).Show
End Sub

With Excel 2003:
It first worked fine until someone hit the 'Enter' key to run the search
(instead of using the mouse to click on the "find next" or "close button" of
the dialog box), then Excel crashed and sent that message suggesting to
report the error to Microsoft.
I tried several times from different computers and got the same error &
crash problem.

Now, even when it seems to work fine (when using the mouse to click on the
buttons rather than pressing the 'Enter' key), I noticed that after running
the macro, I cannot access any more to the Find dialog box using the
Edition/Find or Replace command or CTRL F, until I close Excel and launch it
again.

I tried the code with Excel 2002:
It doesn't cause Excel to crash (even using the 'Enter' key) but I still
can't access the Find command via the menu after running it.

Does anyone have an idea of what might be causing these surprising
problems??

--

By the way, I have another question: why can't I use
Sheet1.range("b:b").Select?? (using the codename)
It returns an error unless Sheet1 is the activesheet (but in this case, why
would I need to mention it?!)

Thank you for your help!

Lydya

Hi all,

Is it possible to provide default values for a user when using "Application.Dialogs(xlDialogSortSpecial).Show" on a "user interface only" protected sheet?

To make it easier for end users in a Finished Goods warehouse to sort info on a stock movement form I'd like to use the following recorded code* to provide default values in the "SortSheet" macro. I'm keeping the sheet protected to prevent formulae being over written which stops sorting of the whole sheet due to the mixing of formulae & input columns on the existing sheet. The use of ...".show" rather than * is to incorporate the possibility of varying the sort options.

*Selection.Sort Key1:=Range("B12"), Order1:=xlDescending, Key2:=Range( _
"D12"), Order2:=xlDescending, Key3:=Range("E12"), Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

Sub SortSheet()
'to allow sheet sorting on the "user interface only" protected sheet & requires use of a _
named range "Sort_Area"
Range("'" & ActiveWorkbook.Name & "'!Sort_Area").Select
Application.Dialogs(xlDialogSortSpecial).Show
'optional MsgBox "Page is sorted as requested."
End Sub

btw, I have offered the above macro as a suggestion in http://www.excelforum.com/excel-general/501367-fix-excel-protection-so-that-it-works-as-published.html.

thanks in advance,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

I am using the Application.Dialogs(xlDialogOpen).Show command in a macro to get a list of files the user can choose to work on. The files that are in the list all have the same password. The files have to have a password on them. Is there a way that the macro can have the password hard coded so the user doesn't have to type in the password everytime they open the file?

Thanks

I am using the code:

Application.Dialogs(xlDialogSendMail).Show

To send the current sheet in an excel spreadsheet, and it is working
fantastically (using Outlook Express).

However I would like to be able to:

1) Add in a standard subject line;
2) Add in a standard body message;
3) Add in an email receiptant (the email address is in a certain cell in the
spreadsheet).

Can anyone give me some feedback on how I can add to the VBA I already have?

Regards

James

Hi,
I have figured out how to get dialog boxes to appear, however I don't know how to extract values. For example:
Application.Dialogs(xlDialogFontProperties).Show
will show a dialog box with font properties. But then what? when the user selects something, how do I retrieve the values selected? Any suggestions or references would be appreciated.
Thanks,
Getty7

Is it possible to use application.Dialogs(xlDialogActivate).Show to bring up the dilog box to select a worksheet? If so please can you expand on the detail and if not then can you sugest an alternative? Thank you

Hi All, I am trying to white a macro that takes a file path from a cell value and opens that file. When there is no file path in the cell, it prompts a dialog box. This part is pretty much working. However, I would like the file path selected from the dialog box to be added to the cell in the original workbook. Thanks in advance.


	VB:
	
 Reports() 
    Dim path1 As String 
    path1 = Range("B20").Value 
     
    On Error Resume Next 
     'checks if there is already a file path'
    Workbooks.Open filename:= _ 
    path1, Updatelinks:=1 
     'if no file path then opens dialog box'
    If Error.Number  0 Then 
        Application.Dialogs(xlDialogOpen).Show Arg1:="*.*" 
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am running a macro on startup from a template that opens the File Save As dialog box to allow the user to select a folder and filename and save it before continuing.

I would like it to default to the Q: drive, it defaults to where the template was opened from.

I used

	VB:
	
Application.Dialogs(xlDialogSaveAs).Show 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to open the dialog, I tried using "Q:" as an argument but it doesn't seem to work.
if I try

	VB:
	
Application.Dialogs(xlDialogOpen).Show "Q:" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the arg seems to work.

any advice on how to change the directory?
thanks

I am looking for some guidance as to how to open the open file dialog and filter the files. The folder has several hundred files and the beginning of the file names are different but the end of the name will end with a "q" or different letter. So my filter would be something like "* q". Here's the code I have to open the dialog to the correct folder, I just don't know how to only show the files ending in " q".


	VB:
	
 OpenRC() 
    Dim dlgAnswer 
    Const strPath As String = "T:CalcsRateCalcs" 
     
    ChDrive strPath 
    ChDir strPath 
     
    dlgAnswer = Application.Dialogs(xlDialogOpen).Show 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your help.

Ryan

I've got the below code:

Sheets("Data").Activate
Cells.Select
Application.Dialogs(xlDialogFormulaFind).Show "Hello"

So when you go onto the sheet "Data" the find diallog box comes up
with "Hello" in it.
There are loads of instances of "Hello" on the Data sheet - how can I
modify the above code, so that when the macro is run the first
instance of Hello has already been found?

Any help greatly appreciated

Jason

I need to create code to bring up a dialog box to SAVE AS
in a specific directory but allow the user to type in
their own filename.

Here is what I have and it doesn't work.

ChDrive "N"
ChDir "N:GROUPRESID BILLING TEAMRes Billing Reps"

Application.Dialogs(xlDialogSaveAs).Show

I've also tried this and doesn't work.

ChDrive "N"
Application.Dialogs
(xlDialogSaveAs).Show "N:GROUPRESID BILLING TEAMRes
Billing Reps")

HELP..Thanks in advance.

Hi

Is there any arguments to I can add to the statement
below for the dialogue's "Look in:" to default to a
specific folder?

Application.Dialogs(xlDialogOpen).Show

Thanks.

Hi

How can I set my Dialogs properties?

This code will show the dialog set to G:
It defaults to files with extentions *.xls
Is there a way to set it to show everything? *.*

Thanks

Using Application.Dialogs(xlDialogWorkbookCopy).Show to bring up the
dialog box asking where the user wants to move the sheet. How do I get
vb to populate the "To book:" and uncheck the "Create a Copy" checkbox?

At the moment when it runs the "To book:" drop down box is automatically
coming up with (new book), so you need to use the drop down box to
select the current workbook.

Any help would be appreciated.

Thanks

Andy

Dear all,

I am using Macro to change file type to "All Files (*.*)" in open dialog box in excel 2007. But it does not work. However it works well for excel 2000 & 2003. The following is the macro:

Sub
Open_File ()
       Application.Dialogs(xlDialogOpen).Show Arg1:="*.*"
End Sub
Appreciate that anyone could help me or give me any idea why it doesn't work in excel 2007?
Thanks!

Hi

Is there any arguments to I can add to the statement
below for the dialogue's "Look in:" to default to a
specific folder?

Application.Dialogs(xlDialogOpen).Show

Thanks.

Hello!

I am using this code to import txt documents, but once the file has been selected and everything, the excel data import wizard shows up... I now would like to get rid of this problem, as well add a standard path to this code, so that I dont always have to start from the desktop and click through tons of folders to get the folder containing my files.

Sub ImportTextFile()

    Dim DestBook As Workbook, SourceBook As Workbook
    Dim DestCell As Range
    Dim RetVal As Boolean

    ' Turn off screen updating.
    Application.ScreenUpdating = False

    ' Set object variables for the active book and active cell.
    Set DestBook = ActiveWorkbook
    Set DestCell = ActiveCell

    ' Show the Open dialog box.
    RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")

    ' If Retval is false (Open dialog canceled), exit the procedure.
    If RetVal = False Then Exit Sub

    ' Set an object variable for the workbook containing the text file.
    Set SourceBook = ActiveWorkbook

    ' Copy the contents of the entire sheet containing the text file.
    Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).Copy

    ' Activate the destination workbook and paste special the values
    ' from the text file.
    DestBook.Activate
    DestCell.PasteSpecial Paste:=xlValues

    ' Close the book containing the text file.
    SourceBook.Close False

End Sub
btw, all the files are delimited and saved in folder C:/testfolder/ . However, I dont want to import all files of this folder, only the one which has been selected.

Thanks for the help!!
A2k

Hi

I have folder which contains downloaded .xlw files ordered by the user each week from a national database. Unfortunately the file names do not contain any relevant information, as they are just file no. and date and time ordeerd. Is there a way to search the files for some user defined text and open then open the relevant file?

The code i use at the moment just sends the user to the folder and they have to keep opening the files until the get the right one. They could order the files one by one but it takes forever.

Here is the code i use now


	VB:
	
 getmims() 
     '
     ' getmims Macro
     ' Macro recorded 09/09/2005 by pwelham
     '
     ' Keyboard Shortcut: Ctrl+g
     
     
    Dim CurrentDir 
    CurrentDir = CurDir 
    ChDir "H:Datamimsrpts" 
    Application.Dialogs(xlDialogOpen).Show 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello.
I use the below macro from a custom button to open a folder


	VB:
	
 Button1_Click() 
    ChDrive "C:" 
    ChDir "C:UsersDefaultDesktop" 
    Application.Dialogs(xlDialogOpen).Show 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to open the folder in windows explorer and not the Open Dialog of excel. What I have to add in the macro for this?

Thanks

I have some code that requires access to the Visual Basic Project. If the user hasn't checked this option in Excel, I'd ideally like to open up the security form for them, with code like the below:


	VB:
	
Application.Dialogs(xlDialogOpen).Show 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I can't find the dialog constant for the security form. Does anyone know it, or have any alternatives? I don't want to use SendKeys.

I have a macro that opens the Open File Dialogue box:

dlgAnswer = Application.Dialogs(xlDialogOpen).Show

I need it to automatically route to a specific folder, "c:Program FilesEasylist" instead of the default "My Documents"

In advance... you guys are geniuses... Thanks!

Hi, I'm trying to display the Save As dialog box and have it automatically show a network path and a filename. I can get it to display the filename I want, but I can't make it change the directory to a network path. It only seems to work with paths in the local drive. Anybody can help?

Here's my code:

	VB:
	
 
Application.Dialogs(xlDialogOpen).Show ("poplarBackups") 
NameArchive = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & "_" & Month(Now()) & "-" & Day(Now()) & "-" &
Year(Now()) 
Application.Dialogs(xlDialogSaveAs).Show arg1:="poplarABVP" & NameArchive 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

When the save as dialog box appears, it shows the last directory used instead of the one I want in a network drive.

My purpose is to give my endusers a workbook with a macro in it which applies changes to existing workbooks on their computers. I want the enduser to select each workbook to apply the changes and have the macro capture the names of the workbooks so they can verify that all of the appropriate workbooks have been changed.

I’m trying to change two types of data: (1) text, (2) formulas.

Here's what little I have, but not much is working.


	VB:
	
 ChangeBACKLOG() 
    MsgBox "Open the FILE to which the Backlog fix is to be applied." 
    Dim targetWorkbook 
    dlgSource = Application.Dialogs(xlDialogOpen).Show 
     
    Set sourceWorkbook = ThisWorkbook 
    Set targetWorkbook = Workbooks.Item(Workbooks.Count) 
     
    On Error Resume Next 
     
    Application.ScreenUpdating = False 
     
    Worksheets("Mo1").Select 
     'I want to enter the phrase Testwords for F9 in cell F9
    Range("F9").Select.Text = "TestTEXT for F9" 
     'I want to enter the phrase Testwords for F11 in cell F11
    Range("F11").Select.Text = "Testwords for F11" 
     'I want to enter the formula =F10 in cell F12
    Range("F12").Select.ActiveCell.FormulaR1C1 = ("=F10") 
     
     '  Then, I want capture the name of each workbook as is opened and corrected
     '  by the enduser and enter that workbook's name in cell A5, then the next in A6, and so on.
     '  The purpose is to give the enduse a list to verify that they have corrected every workbook.
     
     
    Application.ScreenUpdating = True 
    Workbooks.Item(Workbooks.Count).Close SaveChanges:=True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for your help. I'm truly humbled every time I come here and realize how much you guys know and how much I have to learn.
Cmark


No luck finding an answer? You could always try Google.