Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default save workbook but doesn't really save

I have a vb6 program where I merge vba script(that handles formatting)
with an existing excel file. If i turn 'visible' on and manually save
the excel file and re-open it, the formatting is correct. If I merge
the vba script with the excel file and save through code(visible =
false), the vba script is not saved as a part of the excel file thus my
formatting is incorrect. Any ideas?

With ExcelObj
.DisplayAlerts = False
.Visible = False
.Workbooks.Open FileName:=psExcelFName, ReadOnly:=False,
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
.ActiveWorkbook.Save
.ActiveWorkbook.Close
.quit
End With

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 272
Default save workbook but doesn't really save

I don't have vb6 so I guessing a little bit here. I would guess that because
the excel object is not visible there is no "ActiveWorkbook" try dimming a
workbook object then set that to your open statement:

Dim ExcelWorkbook As Excel.Workbook 'Not sure of exact syntax
Set ExcelWorkbook = ExcelObj.Workbooks.Open 'fill in rest from your code
'Do formatting
ExcelWorkbook.Save

Let me know if that works.
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

I have a vb6 program where I merge vba script(that handles formatting)
with an existing excel file. If i turn 'visible' on and manually save
the excel file and re-open it, the formatting is correct. If I merge
the vba script with the excel file and save through code(visible =
false), the vba script is not saved as a part of the excel file thus my
formatting is incorrect. Any ideas?

With ExcelObj
.DisplayAlerts = False
.Visible = False
.Workbooks.Open FileName:=psExcelFName, ReadOnly:=False,
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
.ActiveWorkbook.Save
.ActiveWorkbook.Close
.quit
End With


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default save workbook but doesn't really save


Charles Chickering wrote:
I don't have vb6 so I guessing a little bit here. I would guess that because
the excel object is not visible there is no "ActiveWorkbook" try dimming a
workbook object then set that to your open statement:

Dim ExcelWorkbook As Excel.Workbook 'Not sure of exact syntax
Set ExcelWorkbook = ExcelObj.Workbooks.Open 'fill in rest from your code
'Do formatting
ExcelWorkbook.Save

Let me know if that works.
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

Didn't work...i need to add to files..the original excel file
(psExcelFName) and the vba script(psXLAFName) and you can't add 2 files
to a single workbook only to a workbookS object...so i tried below and
still didn't work. Other ideas?

With ExcelObj
.DisplayAlerts = False
.Visible = False
.Workbooks.Open FileName:=psExcelFName, ReadOnly:=False,
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
.WorkBooks(1).Save
.WorkBooks(1).Close
.Quit
End With



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 272
Default save workbook but doesn't really save

Ok, I've tried to simulate your problem by accessing Excel from Outlook.
Here's what I came up with for code:
Dim ExcelObj As Object
Dim ExcelWB As Object
Set ExcelObj = CreateObject("Excel.Application")

With ExcelObj
.DisplayAlerts = False
.Visible = False
Set ExcelWB = .Workbooks.Open _
FileName:=psExcelFName, ReadOnly:=False, _
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
ExcelWB.Save
ExcelWB.Close
.quit
End With


Does that work in VB?
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:


Charles Chickering wrote:
I don't have vb6 so I guessing a little bit here. I would guess that because
the excel object is not visible there is no "ActiveWorkbook" try dimming a
workbook object then set that to your open statement:

Dim ExcelWorkbook As Excel.Workbook 'Not sure of exact syntax
Set ExcelWorkbook = ExcelObj.Workbooks.Open 'fill in rest from your code
'Do formatting
ExcelWorkbook.Save

Let me know if that works.
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

Didn't work...i need to add to files..the original excel file
(psExcelFName) and the vba script(psXLAFName) and you can't add 2 files
to a single workbook only to a workbookS object...so i tried below and
still didn't work. Other ideas?

With ExcelObj
.DisplayAlerts = False
.Visible = False
.Workbooks.Open FileName:=psExcelFName, ReadOnly:=False,
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
.WorkBooks(1).Save
.WorkBooks(1).Close
.Quit
End With




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default save workbook but doesn't really save

What do you mean by "i need to add to files" ?
Are you trying to insert a worksheet into the open workbook ?
Or just open another WB ?

Is this an add-in ? As it has a name of psXLAFName.

Also, you do not need the () in
..Run ("FormatReportScript")

Also, it may depend on how/what "FormatReportScript" does.

But as Charles says, it is easier to give yourself some object variables to
work with, otherwise statements like ".ActiveWorkbook.Save" are not obvious
as to which WB is being affected.

NickHK

wrote in message
ups.com...

Charles Chickering wrote:
I don't have vb6 so I guessing a little bit here. I would guess that

because
the excel object is not visible there is no "ActiveWorkbook" try dimming

a
workbook object then set that to your open statement:

Dim ExcelWorkbook As Excel.Workbook 'Not sure of exact syntax
Set ExcelWorkbook = ExcelObj.Workbooks.Open 'fill in rest from your code
'Do formatting
ExcelWorkbook.Save

Let me know if that works.
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

Didn't work...i need to add to files..the original excel file
(psExcelFName) and the vba script(psXLAFName) and you can't add 2 files
to a single workbook only to a workbookS object...so i tried below and
still didn't work. Other ideas?

With ExcelObj
.DisplayAlerts = False
.Visible = False
.Workbooks.Open FileName:=psExcelFName, ReadOnly:=False,
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
.WorkBooks(1).Save
.WorkBooks(1).Close
.Quit
End With







  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default save workbook but doesn't really save


NickHK wrote:
What do you mean by "i need to add to files" ?
Are you trying to insert a worksheet into the open workbook ?
Or just open another WB ?

Is this an add-in ? As it has a name of psXLAFName.

Also, you do not need the () in
.Run ("FormatReportScript")

Also, it may depend on how/what "FormatReportScript" does.

But as Charles says, it is easier to give yourself some object variables to
work with, otherwise statements like ".ActiveWorkbook.Save" are not obvious
as to which WB is being affected.

NickHK

wrote in message
ups.com...

Charles Chickering wrote:
I don't have vb6 so I guessing a little bit here. I would guess that

because
the excel object is not visible there is no "ActiveWorkbook" try dimming

a
workbook object then set that to your open statement:

Dim ExcelWorkbook As Excel.Workbook 'Not sure of exact syntax
Set ExcelWorkbook = ExcelObj.Workbooks.Open 'fill in rest from your code
'Do formatting
ExcelWorkbook.Save

Let me know if that works.
--
Charles Chickering

"A good example is twice the value of good advice."


" wrote:

Didn't work...i need to add to files..the original excel file
(psExcelFName) and the vba script(psXLAFName) and you can't add 2 files
to a single workbook only to a workbookS object...so i tried below and
still didn't work. Other ideas?

With ExcelObj
.DisplayAlerts = False
.Visible = False
.Workbooks.Open FileName:=psExcelFName, ReadOnly:=False,
Password:="", IgnoreReadOnlyRecommended:=True
.Workbooks.Add psXLAFName
.Run ("FormatReportScript")
.WorkBooks(1).Save
.WorkBooks(1).Close
.Quit
End With


I meant I need to add two files(the initial open of the spreadsheet and the xla file)..sorry. I suspect part of my problem is that I don't fully understand the difference in a macro and an addin. I've seen 'addin' examples of code( and tried them) but with no luck. Again, what I'm trying to do is add some vba script(see below) to an existing excel spredsheet and save the results. The vba script simply changes font settings, column sizes etc. When the code hits the 'run' statement, the excel spreadsheet flashes on the screen(formatted correctely) and then disappears, but when I open it up with excel by double-clicking...the formatting is gone and the vba script is not saved with the spreadsheet.


Public Function CSSReportFormat()
Dim oSheet As Object
Dim oRange As Range
Dim i As Long
Dim StartRow As Long
Dim nReports As Long


On Error Resume Next

'make the worksheet invisible
Excel.Application.Visible = False

'get a reference to the activeworksheet
Set oSheet = Excel.ActiveWorkbook.Sheets(1)

'resize all the fonts
oSheet.Cells.Select
Excel.Selection.Font.Name = "Courier New"
Excel.Selection.Font.Size = 8

'resize the columns
oSheet.Columns(1).ColumnWidth = 13.86
oSheet.Columns(2).ColumnWidth = 11.71

'apply special stuff to the header row
oSheet.Range(oSheet.Cells(2, 5), oSheet.Cells(6, 5)).Select
Excel.Selection.Font.Bold = True
oSheet.Rows(8).Select
Excel.Selection.Font.Bold = True
oSheet.Cells(8, 3).Select
Excel.Selection.WrapText = True

'oSheet.Rows(1).Select
oSheet.Range("A8:L8").Borders(xlEdgeTop).Weight = xlThick
oSheet.Range("A8:L8").Borders(xlEdgeBottom).Weight = xlThick

oSheet.Cells(1, 1).Select

Excel.Application.Visible = True

End Function

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4
Default save workbook but doesn't really save

I tried Charles method but with no luck...seems to work with no errors
but my results are still not saved.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Shared workbook - to save or not to save bluebird Excel Discussion (Misc queries) 1 November 14th 06 07:17 PM
"Save" macro problem, still prompted to save when closing workbook (?) StargateFanFromWork[_4_] Excel Programming 8 September 13th 06 04:49 PM
Disable save, save as, but allow save via command button TimN Excel Programming 10 September 1st 06 07:05 PM
How to diasble save and save as menu but allow a save button hon123456 Excel Programming 1 June 12th 06 09:50 AM
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: harpscardiff[_10_] Excel Programming 8 November 10th 05 01:24 PM


All times are GMT +1. The time now is 01:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"