Format Exported Excel File

[mubi_masti]

I have used following text to format the exported file but I am facing an error i.e a message appears called “"9-subscript out of range"..


How this error can be resolved.


Public Sub ModifyExportedExcelFileFormats(sFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats

Application.SetOption "Show Status Bar", True

vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)

With xlApp
.Application.Sheets("YourSheetName").Select
.Application.Cells.Select
.Application.Selection.ClearFormats
.Application.Rows("1:1").Select
.Application.Selection.Font.Bold = True
.Application.Cells.Select
.Application.Selection.RowHeight = 12.75
.Application.Selection.Columns.AutoFit
.Application.Range("A2").Select
.Application.ActiveWindow.FreezePanes = True
.Application.Range("A1").Select
.Application.Selection.AutoFilter

.Application.Activeworkbook.Save
.Application.Activeworkbook.Close
.Quit
End With

Set xlApp = Nothing
Set xlSheet = Nothing

vStatusBar = SysCmd(acSysCmdClearStatus)

Exit_ModifyExportedExcelFileFormats:
Exit Sub

Err_ModifyExportedExcelFileFormats:
vStatusBar = SysCmd(acSysCmdClearStatus)
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ModifyExportedExcelFileFormats

End Sub

 

[vbaInet]

Where did you get the code from?

Also, replace this part:

With xlapp.Application
    .Sheets("YourSheetName").Select
    .Cells.Select
    .Selection.ClearFormats
    .Rows("1:1").Select
    .Selection.Font.Bold = True
    .Cells.Select
    .Selection.RowHeight = 12.75
    .Selection.Columns.AutoFit
    .Range("A2").Select
    .ActiveWindow.FreezePanes = True
    .Range("A1").Select
    .Selection.AutoFilter
    
    .Activeworkbook.Save
    .Activeworkbook.Close
    .Quit
End With

Then, remove the following line from your code, run it, when it errors click Debug and tell us the line it highlights:

[FONT=Times New Roman][SIZE=3]On Error GoTo Err_ModifyExportedExcelFileFormats[/SIZE][/FONT]

 

[mubi_masti]

thanks for your reply.

I have pasted this code from a thread of same forum " hiding excel manipulations and showing a progress bar"

I have modified the coded according to suggession but same error occur after pressing debug the line higlighted was

.sheets("YourSheetName").select


can you help me in removing this error

 

[vbaInet]

.sheets("YourSheetName").select

Because you should put the name of the sheet that you want to format in YourSheetName.

 

[mubi_masti]

Thanks once again

You have correctly identify the problem. I have modify the code but once again an error appears.

The error is exported file is 2003 format and save as .xls but message appears the access does not file .xlsx is not found.

How i can improve this error. error is attached as well.

 

[vbaInet]

I said, YourSheetName should be the name of the Worksheet inside your spreadsheet. Not the name of the spreadsheet.

I can't continue to explain what the code does. You need to go back to where you got the code from and follow the instructions on there.

 

[mubi_masti]

Thanks for your reply,

I have resolved all issues and only one error left and that is "object does'nt support this property or method" and after debugging following line is highlighted.


.Activeworkbook.Save

I thinks this problem can be solved and your can guide me better

 

[vbaInet]

Try:

.xlApp.Parent.ActiveWorkbook.Save

If that doesn't work, paste your whole code here (make sure it includes the new amendments too) and wrap them in code tags as explained in the thread below:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[mubi_masti]

Great!!!!!!!!!!!!

I am able to format excel file exproted from access with your help

Thanks a lot

Can I change the view of exported worksheet from Normal to page break view, zoom and board and shading of those cell which have data only instead of whole sheet.

If yes then what will the code for that or any link where i can find help regarding following method

.Application.selection.........

Thanks once again

 

[vbaInet]

Good to hear!

I bet you can but I don't know what the methods or properties are called. I don't do Excel and this section of the forum is for Access, not Excel.

I would imagine that all the properties you want to change will be methods/properties of the Worksheets collection. To affect only cells that have data I think you need the UsedRange method (or something like that) and it's part of the Worksheet object too.

If you don't succeed in finding the methods/properties, I suggest creating a new thread in the Excel section of the forum.