open commondialog code error.how do i fix this?

[six888]

i have a problem with my code... i use this code to open an excel and manipulate the data

Dim xl3 As Excel.Application
Dim xl3sheet As Excel.Worksheet
Dim xl3wbook As Excel.Workbook
Dim FilNam As String

Me.CommonDialog1.ShowOpen
FilNam = Me.CommonDialog1.Filename

If Me.CommonDialog1.Filename <> "" Or Me.CommonDialog1.Filename <> ".xls" Then

Set xl3 = CreateObject("Excel.Application")
Set xl3wbook = xl3.Workbooks.Open(FilNam)
Set xl3sheet = xl3wbook.Sheets.Item(1)

xl3.Worksheets("SYS_SUMM").Activate
xl3.Range("G25:L27").Copy
....... 'manipulating data

Application.CutCopyMode = False
xl15.DisplayAlerts = False
xl15.Workbooks.Close
xl15.Quit

Set xl15 = Nothing
Set xl15wbook = Nothing
Set xl15sheet = Nothing

End If
End Sub

it works well accept a few things
when the browse menu opened so i can choose which file to manipulate

if i double click the file i want to open = its open and good
if i click a file once and click button open = its open and good
if i didnt click anything and close the open window = its error to my application
if i click a file and click cancel = its error to my application

how do i fix this?

another question is when the commondialog opened. how can i set it so user can only choose excel file only.this is to reduce human error.

thanks

[yello]

if i didnt click anything and close the open window = its error to my application
if i click a file and click cancel = its error to my application

These two errors can be fixed by the following condition
FilNam = Me.CommonDialog1.Filename
If FilNam =False Then
Exit Sub
End If

another question is when the commondialog opened. how can i set it so
user can only choose excel file only.this is to reduce human error.

Use the Filter Property to ".xls"

[six888]

hey yello thanks for your help in the commondialog open

my project is now 98% complete thanks to you. however i have 2 concern.
i hope you can help me on this.

1) i cant find a way to make commondialog for save as. i know it should be quite similar to the commondialog open that but i cant make it out. i find a few code on the net but cant be used because most of them use

ActiveWorkbook.SaveAs

while my program is closing another workbook that is not active. it should start with something like this i guess

excel2.????.????

2) my program take too long to execute. and sometimes its shows "waiting another ole to execute" msgbox. i dont know why. at 1st i thought it was because of insufficient RAM. but maybe something wrong with my code

[yello]

Instead of
Me.CommonDialog1.ShowOpen

you can use
Me.CommonDialog1.ShowSave

This will open the save dialog box.

If you post the complete code then I can check why it is taking long time

[six888]

im actually building a reporting automation

this is my code for open new excel.it works well

Dim xln As Excel.Application
Dim xlnwbook As Excel.Workbook
Dim xlnwSheet As Excel.Worksheet

Set xln = CreateObject("Excel.Application")
Set xlnwbook = xln.Workbooks.Add

then i open other excel file and copy some data to the new excel file i just opened.

then i want to save the new excel file
here is the code

Set xln = CreateObject("Excel.Application")

CommonDialog1.Filter = "Microsoft Office Excel (*.xls)|*.xls"

Me.CommonDialog1.ShowSave
FilNam = Me.CommonDialog1.Filename
xln.Application.ActiveWorkbook.SaveAs (FilNam)
xln.Application.ActiveWorkbook.Save

Me.CommonDialog1.Filename = ""
If CloseMode <> 1 Then Cancel = 1
End Sub

but i wont work.please help

[six888]

hey... now i can save with this code

Set xln = CreateObject("Excel.Application")

CommonDialog1.Filter = "Microsoft Office Excel (*.xls)|*.xls"
Me.CommonDialog1.Filename = "report"

Me.CommonDialog1.ShowSave
FilNam = Me.CommonDialog1.Filename
xlnwbook.SaveAs (FilNam)

Set xln = Nothing
Set xlnwbook = Nothing
Set xlnsheet = Nothing

but there is still some problem
because i set filename default to report.xls.
whenever i just click ok when there is already a file name report.xls
it will be error

however...

the other way... i put filename default to "" and i type report.xls myself
it will produce a msgbox of asking if its ok to replace existing the file
if i click ok.. then it save.. if i click no.. then it error

what i suppose to do to not produce error in this two occasion?

[yello]

Its all your selection to replace the file or not.
But you avoid this message box from excel.
After you create an instance of excel ie,
Set xln = CreateObject("Excel.Application")

Add the following line also

xln.DisplayAlerts=False

[six888]

is there any way so that i can make it possible to replace the existing? i mean without making error not hide the error

and also for the user to say no on replace existing file and the program automaticly lead user back to the save as? not just hide the error