- Home
- /
- Architecture, Engineering & Construction
- /
- AutoCAD Forums
- /
- VBA Forum
- /
- Open new then save with name
Open new then save with name
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Open new then save with name
Not applicable
528 Views
9 Replies
529 Views
9 Replies
Replies (9)
Message 2 of 10
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Message 3 of 10
Not applicable
02-16-2006
11:44 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
The following is a excerpt from Excel 2003 VBA Help
for Workbook.SaveAs
for Workbook.SaveAs
Saves changes to the workbook in a different file.
expression.SaveAs(
class=bterm>FileName, FileFormat,
class=bterm>Password, WriteResPassword,
class=bterm>ReadOnlyRecommended,
class=bterm>CreateBackup, AccessMode,
class=bterm>ConflictResolution, AddToMru,
TextCodepage,
class=bterm>TextVisualLayout,
class=bterm>Local)
expression Required. An expression that
returns one of the above objects.
Filename
Optional Variant. A string that indicates the name of the
file to be saved. You can include a full path; if you don't, Microsoft Excel
saves the file in the current folder.
FileFormat
Optional Variant. The file format to use when you save the
file. For a list of valid choices, see the
href="mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaxl10.chm::/html/xlproFil...
property. For an existing file, the default format is the last file format
specified; for a new file, the default is the format of the version of Excel
being used.
Password
Optional Variant. A case-sensitive string (no more than 15
characters) that indicates the protection password to be given to the
file.
WriteResPassword
Optional Variant. A string that indicates the
write-reservation password for this file. If a file is saved with the password
and the password isn't supplied when the file is opened, the file is opened as
read-only.
class=runinhead>ReadOnlyRecommended Optional
class=bterm>Variant. True to display a message when the
file is opened, recommending that the file be opened as read-only.
CreateBackup
Optional Variant. True to create a backup
file.
class=runinhead>AccessMode Optional
class=DropDown onkeypress=Outline2() onclick=Outline2()
href="mk:@MSITStore:C:\Program%20Files\Mic...
size=1>.
| XlSaveAsAccessMode can be one of these XlSaveAsAccessMode constants. |
| xlExclusive (exclusive mode) |
| xlNoChange default (don't change the access mode) |
| xlShared (share list) If this argument is omitted, the access mode isn't |
class=runinhead>ConflictResolution Optional
href="mk:@MSITStore:C:\Program%20Files\Microsoft%20Office\OFFICE11\1033\vbaxl10.chm::/html/xlmthSav...
size=1>.
| XlSaveConflictResolution can be one of these XlSaveConflictResolution constants. |
| xlUserResolution (display the conflict-resolution dialog box) |
| xlLocalSessionChanges (automatically accept the local user's changes) |
| xlOtherSessionChanges (accept other changes instead of the local user's changes) If this argument is omitted, the conflict-resolution |
AddToMru
Optional Variant. True to add this
workbook to the list of recently used files. The default value is
class=bterm>False.
TextCodePage
Optional Variant. Not used in U.S. English Microsoft
Excel.
TextVisualLayout
Optional Variant. Not used in U.S. English Microsoft
Excel.
Local Optional
class=bterm>Variant. True saves files against the
language of Microsoft Excel (including control panel settings).
class=bterm>False (default) saves files against the language of
class=glossary onkeypress="AppendPopup(this,'ofVBA')"
onclick="AppendPopup(this,'ofVBA')"
href="m...
(which is
typically US English unless the VBA project where Workbooks.Open is run from is
an old internationalized XL5/95 VBA project).
Hopefully this helps.
--
Phil Custer II, P.E.
Custer Services,
Inc.
href="mailto:custer@landfillgas.com">custer@landfillgas.com
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
"David M. Gardner" <
href="mailto:dgardner@birdairDOTcom">dgardner@birdairDOTcom> wrote in
message
href="news:5088655@discussion.autodesk.com">news:5088655@discussion.autodesk.com...
With the following code I can end up opening a
new excel file. Now I would like to save that file. How do I do
that? I have looked in excel vba help with no help from it. I'm
doing this from an ACAD vba module.
--
Thanks,
David M.
Gardner
Change the DOT to reply
Message 4 of 10
Not applicable
02-16-2006
12:40 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
If Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
wrote in message news:5088689@discussion.autodesk.com...
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
If Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Message 5 of 10
Not applicable
02-17-2006
06:47 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner" wrote in message
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
wrote in message news:5088689@discussion.autodesk.com...
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner"
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Message 6 of 10
Not applicable
02-21-2006
05:53 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner" wrote in message
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
wrote in message news:5088689@discussion.autodesk.com...
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner"
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Message 7 of 10
Not applicable
02-21-2006
06:26 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Ah, you may want to try to qualify the Workbooks with excelApp. That is,
Replace:
Workbooks....
with:
excelApp.Workbooks...
"David M. Gardner" wrote in message
news:5091764@discussion.autodesk.com...
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner" wrote in message
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
wrote in message news:5088689@discussion.autodesk.com...
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Replace:
Workbooks....
with:
excelApp.Workbooks...
"David M. Gardner"
news:5091764@discussion.autodesk.com...
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner"
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Message 8 of 10
Not applicable
02-21-2006
07:47 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
OK. I don't get the error anymore but it does not save the file.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5091822@discussion.autodesk.com...
Ah, you may want to try to qualify the Workbooks with excelApp. That is,
Replace:
Workbooks....
with:
excelApp.Workbooks...
"David M. Gardner" wrote in message
news:5091764@discussion.autodesk.com...
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner" wrote in message
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
wrote in message news:5088689@discussion.autodesk.com...
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5091822@discussion.autodesk.com...
Ah, you may want to try to qualify the Workbooks with excelApp. That is,
Replace:
Workbooks....
with:
excelApp.Workbooks...
"David M. Gardner"
news:5091764@discussion.autodesk.com...
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner"
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Message 9 of 10
Not applicable
02-21-2006
11:32 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Here is revised code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Could not start Excel", vbExclamation
''End
Exit Sub 'or Function. NEVER use "End"
End If
End If
excelApp.Visible = True
'' if the file strDWGIndex refers to does not exist, you will get error
on next line
Set wbkObj = excelApp.Workbooks.Open(FileName:=strDWGIndex)
''So handle the error if file not found
If Err.Number<>0 Then
If excelApp.Workbooks.COUNT = 0 Then
Set wbkObj=Workbooks.Add ("X:\drawing index.xls") ' 'You must
guarrantee "X:\drawing Index.xls" always exists
''Workbooks.SaveAs FileName:=strDWGIndex ''Workbooks does not
have a method called SaveAs(), Workbook does
wbkObj.SaveAs FileName:=strDWGIndex
End If
End If
'if you want Excel and the workbook to stay open. that is fine.
Otherwise, you should close it
'wbkObj.Close False ''Save/not save while closing
'excelApp.Quit ''Quit Excel application
Set wbkObj = Nothing
Set excelApp = Nothing
"David M. Gardner" wrote in message
news:5091937@discussion.autodesk.com...
OK. I don't get the error anymore but it does not save the file.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5091822@discussion.autodesk.com...
Ah, you may want to try to qualify the Workbooks with excelApp. That is,
Replace:
Workbooks....
with:
excelApp.Workbooks...
"David M. Gardner" wrote in message
news:5091764@discussion.autodesk.com...
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner" wrote in message
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
wrote in message news:5088689@discussion.autodesk.com...
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Could not start Excel", vbExclamation
''End
Exit Sub 'or Function. NEVER use "End"
End If
End If
excelApp.Visible = True
'' if the file strDWGIndex refers to does not exist, you will get error
on next line
Set wbkObj = excelApp.Workbooks.Open(FileName:=strDWGIndex)
''So handle the error if file not found
If Err.Number<>0 Then
If excelApp.Workbooks.COUNT = 0 Then
Set wbkObj=Workbooks.Add ("X:\drawing index.xls") ' 'You must
guarrantee "X:\drawing Index.xls" always exists
''Workbooks.SaveAs FileName:=strDWGIndex ''Workbooks does not
have a method called SaveAs(), Workbook does
wbkObj.SaveAs FileName:=strDWGIndex
End If
End If
'if you want Excel and the workbook to stay open. that is fine.
Otherwise, you should close it
'wbkObj.Close False ''Save/not save while closing
'excelApp.Quit ''Quit Excel application
Set wbkObj = Nothing
Set excelApp = Nothing
"David M. Gardner"
news:5091937@discussion.autodesk.com...
OK. I don't get the error anymore but it does not save the file.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5091822@discussion.autodesk.com...
Ah, you may want to try to qualify the Workbooks with excelApp. That is,
Replace:
Workbooks....
with:
excelApp.Workbooks...
"David M. Gardner"
news:5091764@discussion.autodesk.com...
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner"
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
Message 10 of 10
Not applicable
02-21-2006
12:32 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thank you.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5092347@discussion.autodesk.com...
Here is revised code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Could not start Excel", vbExclamation
''End
Exit Sub 'or Function. NEVER use "End"
End If
End If
excelApp.Visible = True
'' if the file strDWGIndex refers to does not exist, you will get error
on next line
Set wbkObj = excelApp.Workbooks.Open(FileName:=strDWGIndex)
''So handle the error if file not found
If Err.Number<>0 Then
If excelApp.Workbooks.COUNT = 0 Then
Set wbkObj=Workbooks.Add ("X:\drawing index.xls") ' 'You must
guarrantee "X:\drawing Index.xls" always exists
''Workbooks.SaveAs FileName:=strDWGIndex ''Workbooks does not
have a method called SaveAs(), Workbook does
wbkObj.SaveAs FileName:=strDWGIndex
End If
End If
'if you want Excel and the workbook to stay open. that is fine.
Otherwise, you should close it
'wbkObj.Close False ''Save/not save while closing
'excelApp.Quit ''Quit Excel application
Set wbkObj = Nothing
Set excelApp = Nothing
"David M. Gardner" wrote in message
news:5091937@discussion.autodesk.com...
OK. I don't get the error anymore but it does not save the file.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5091822@discussion.autodesk.com...
Ah, you may want to try to qualify the Workbooks with excelApp. That is,
Replace:
Workbooks....
with:
excelApp.Workbooks...
"David M. Gardner" wrote in message
news:5091764@discussion.autodesk.com...
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan" wrote in message
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner" wrote in message
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
wrote in message news:5088689@discussion.autodesk.com...
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5092347@discussion.autodesk.com...
Here is revised code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
MsgBox "Could not start Excel", vbExclamation
''End
Exit Sub 'or Function. NEVER use "End"
End If
End If
excelApp.Visible = True
'' if the file strDWGIndex refers to does not exist, you will get error
on next line
Set wbkObj = excelApp.Workbooks.Open(FileName:=strDWGIndex)
''So handle the error if file not found
If Err.Number<>0 Then
If excelApp.Workbooks.COUNT = 0 Then
Set wbkObj=Workbooks.Add ("X:\drawing index.xls") ' 'You must
guarrantee "X:\drawing Index.xls" always exists
''Workbooks.SaveAs FileName:=strDWGIndex ''Workbooks does not
have a method called SaveAs(), Workbook does
wbkObj.SaveAs FileName:=strDWGIndex
End If
End If
'if you want Excel and the workbook to stay open. that is fine.
Otherwise, you should close it
'wbkObj.Close False ''Save/not save while closing
'excelApp.Quit ''Quit Excel application
Set wbkObj = Nothing
Set excelApp = Nothing
"David M. Gardner"
news:5091937@discussion.autodesk.com...
OK. I don't get the error anymore but it does not save the file.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5091822@discussion.autodesk.com...
Ah, you may want to try to qualify the Workbooks with excelApp. That is,
Replace:
Workbooks....
with:
excelApp.Workbooks...
"David M. Gardner"
news:5091764@discussion.autodesk.com...
Thank you for that bit of info. But that does not answer/fix the error I
get. If you can look at my 2nd post that has the error attached.
--
Thanks,
David M. Gardner
Change the DOT to reply
"Norman Yuan"
news:5089506@discussion.autodesk.com...
Here is the problem:
If Workbooks.COUNT = "0" Then
Workbooks.Count is a number value, not a text string value. That is why
the...Add() and ...SaveAs() are never executed. It should be:
If Workbooks.Count=0 Then...
The other problen is to use "On Error Resume Next" without trying to handle
possible error.
In you case, the statement
If Workbooks.Count="0"
generates a error fo "Type misnatch", but the process simply RESUMEs to next
line and continue, so you do not notice something wrong has happened.
My thumb of rule is to not use "On Error Resume Next" unless you try to
examine Err object is each following line of code that could reuilt in
runtime exception.
"David M. Gardner"
news:5088791@discussion.autodesk.com...
I'm using this code:
Dim excelApp As Object
Dim wbkObj As Object
Dim shtObj As Object
On Error Resume Next
Set excelApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Err.Clear
Set excelApp = CreateObject("Excel.Application")
If Err <> 0 Then
MsgBox "Could not start Excel", vbExclamation
End
End If
End If
excelApp.Visible = True
Set wbkObj = Workbooks.Open(FileName:=strDWGIndex)
I
f Workbooks.COUNT = "0" Then
Workbooks.Add ("X:\drawing index.xls")
Workbooks.SaveAs FileName:=strDWGIndex
End If
Set wbkObj = Nothing
Set excelApp = Nothing
and I get this error highlighting the ".saveas"
--
Thanks,
David M. Gardner
Change the DOT to reply
David,
There are three methods for saving an excel file/workbook
Save, SaveAs,SaveCopyAs
ActiveWorkbook.Save
WorkBook.SaveAs Filename:=NewFileName
ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
In addition to close a workbook with out saving use
Workbooks("BOOK1.XLS").Close SaveChanges:=False
To find methods these use the object browser in Excel IDE (VBA) environment
Fred Castillo