Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 2999

Oct 23rd, 2007

open commondialog code error.how do i fix this?

Expand Post »

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

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
 
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
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

Last edited by six888; Oct 23rd, 2007 at 11:13 pm.

Similar Threads

Code 19 error with USB digital camera in Windows NT / 2000 / XP
Filename as a constant? in C++
SS tabs in Visual Basic 4 / 5 / 6
help me ffix this in C++
Some stupid error i cant fix, PLEASE HELP in Java

six888

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Sep 2007

Permalink

Oct 24th, 2007

Re: open commondialog code error.how do i fix this?

Quote ...

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

Quote ...

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"

yello

Reputation Points: 13

Solved Threads: 4

Light Poster

Offline

30 posts
since Nov 2006

Permalink

Oct 24th, 2007

Re: open commondialog code error.how do i fix this?

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

six888

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Sep 2007

Permalink

Oct 24th, 2007

Re: open commondialog code error.how do i fix this?

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

yello

Reputation Points: 13

Solved Threads: 4

Light Poster

Offline

30 posts
since Nov 2006

Permalink

Oct 24th, 2007

Re: open commondialog code error.how do i fix this?

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Sep 2007

Permalink

Oct 24th, 2007

Re: open commondialog code error.how do i fix this?

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?

six888

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Sep 2007

Permalink

Oct 24th, 2007

Re: open commondialog code error.how do i fix this?

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

yello

Reputation Points: 13

Solved Threads: 4

Light Poster

Offline

30 posts
since Nov 2006

Permalink

Oct 25th, 2007

Re: open commondialog code error.how do i fix this?

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

six888

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Sep 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: How to Copy an Excel range into an array

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Open file from listbox

DaniWeb Message

Cancel Changes

User Name
Password