saving after formating columns?

[geloman1]

I cant seem to save the changes I made to a worksheet in vb6 .
I have an existing excel file wich I have generated. I then need to change the
column width , then I save the work book. Yet when I re-open that file it allways reverts back to the old column width. heres the code

Dim Workbook As Excel.Workbook
Set Excel = New Excel.Application
Set Workbook = Excel.Workbooks.Open(App.Path & "\Report_W8.xls")

Dim Worksheet As Worksheet
Set Worksheet = Workbook.ActiveSheet

Worksheet.Rows.ColumnWidth = 15
Workbook.Activate
Workbook.Save


Workbook.Close
Excel.Quit
Set Excel = Nothing

[theDude]

See change in BOLD below:

Dim Workbook As Excel.Workbook
Set Excel = New Excel.Application
Set Workbook = Excel.Workbooks.Open(App.Path & "\Report_W8.xls")

Dim Worksheet As Worksheet
Set Worksheet = Workbook.ActiveSheet

Worksheet.Rows.ColumnWidth = 15
Workbook.Activate
Workbook.Save


Workbook.Close SaveChanges:=True
Excel.Quit
Set Excel = Nothing

Hope this helps,
theDude

[geloman1]

This Helps partially and thank you , but it seems that the column width is not kept in the save I tried adding some text and it saved it , also during I do a
Excel.Visible = True, to see its affect but when I re open the excel , the column width is gone but the added text is there.

Dim Workbook As Excel.Workbook
Set Excel = New Excel.Application
Set Workbook = Excel.Workbooks.Open(App.Path & "\Report_W8.xls")

Dim Worksheet As Worksheet
Set Worksheet = Workbook.ActiveSheet

Worksheet.Rows.Cells(1, 5) = "SOME TEXT"
Worksheet.Rows.ColumnWidth = 15
Worksheet.Range("A1", "C500").AutoFormat
Workbook.Activate

Excel.Visible = True

Workbook.Save
Workbook.Close SaveChanges:=True

Excel.Quit
Set Excel = Nothing

[theDude]

Change your line of code:

Worksheet.Rows.ColumnWidth = 15


to this:

Worksheet.Columns("A:E").ColumnWidth = 15

Remember to change the column reference "A:E" to your own....

Hope this helps,
theDude

[geloman1]

thanks dude, I realized the problem had to do with something I ommited,
I orriginaly generated the excel from a Recordset and wrote each line into a file
with an xls. extension using the filesystem object in vb6, wich saved it as a excel file, but for some reason it saved as a tab delemited file with an xls extension even though it opend directly in xls,

tab delemimted format does not allow for any formating , which is why I would see the formating but not once it was saved. I solved this by
inserting the records directly into the excel worksheet and then formating.


Set Excel = New Excel.Application
Set Workbook = Excel.Workbooks.Add

Dim Worksheet As Worksheet
Set Worksheet = Workbook.ActiveSheet

j = 1


Do While oRs3.EOF <> True

' write to file

For i = 0 To oRs3.Fields.Count - 1
CurrentField = oRs3(i)
Worksheet.Rows.Cells(j, i + 1) = CurrentField
Next i


oRs3.MoveNext
j = j + 1
Loop

oConn.Close
Set oConn = Nothing

Excel.Columns("A:C").Select
Excel.Selection.Columns.AutoFit

Workbook.SaveAs (App.Path & "\Report_W8.xls")

Workbook.Close SaveChanges:=True
Excel.DisplayAlerts = False