Formatting excel using access

S

Summer123

Registered User.
Local time
Today, 02:36
Joined
Feb 9, 2011
Messages
216
Hello,
with help of many folks on this website I was able to export many queries into different tabs on excel. However i am stuck at how i can format excel. Currently my code only formats the first line in excel but after that is done, i would like it to format the entire sheet with the following

font - Tahoma
size - 10

does anyone know how i can accompish this?

xlBook.Worksheets(intCurrentSheet).Activate
With xlApp.ActiveSheet
.Columns.Select
.Columns.EntireColumn.AutoFit
.Rows.Select
.Rows(1).Font.Bold = True
.Rows(1).Font.Size = 10
.Rows(1).Font.Name = "Verdana"
.Range("A1").Select 'Selects first cell to deselect the rest of the sheet.
End With
intCurrentSheet = intCurrentSheet + 1
Loop
xlBook.Worksheets(1).Activate 'Returns the selection to the first worksheet.
 
First of all you just want to format the parts in use or else it will make your spreadsheet file size grow immensely.

So, you probably should use this before you format your column headers:
Code: 
With xlApp.ActiveSheet.UsedRange.Font
             .Name = "Tahoma"
             .Size = 10
 End With
 
worked like a charm Bob. thank you! one question in my code i have this line ".Columns.EntireColumn.AutoFit" however it doesnt expand the columns to their fullest, do i have it coded incorrectly?

Also, everytime i have to delete the spreadsheet if i want to export it out again, is there a simple way to delet the spreadsheet out of my c drive automatically when i run the code again?
 
Summer123 said:
worked like a charm Bob. thank you! one question in my code i have this line ".Columns.EntireColumn.AutoFit" however it doesnt expand the columns to their fullest, do i have it coded incorrectly?
Click to expand...
Have you selected the entire area?
xlApp.ActiveSheet.Cells.Select

Also, everytime i have to delete the spreadsheet if i want to export it out again, is there a simple way to delet the spreadsheet out of my c drive automatically when i run the code again?
Click to expand...

You can use:

Code: 
If Dir(strYourPathAndFileNameHere) <> vbNullString Then
   Kill(strYourPathAndFileNameHere)
End If
 
thank you Bob.

Have you selected the entire area?
xlApp.ActiveSheet.Cells.Select
Click to expand...

for the above i've tried everything, here is where i am at now but still its not working and auto fitting the columns. Am I doing something wrong?

Code: 
If strFileName = "SCF Records.xls" Then
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(fileIn)
    Dim intCountofSheets, intCurrentSheet As Integer
    intCountofSheets = xlBook.sheets.Count
    intCurrentSheet = 1
    Do While intCurrentSheet <= intCountofSheets
    xlBook.Worksheets(intCurrentSheet).Activate
        With xlApp.ActiveSheet.UsedRange.Font
             .Name = "Tahoma"
             .Size = 10
        End With
        With xlApp.ActiveSheet.Cells.Select
            .columns.autofit
            .rows.autofit
        End With
        With xlApp.ActiveSheet
'            .columns.Select
'            .columns.EntireColumn.autofit
            .rows.Select
            .rows(1).Font.Bold = True
            .rows(1).Font.Size = 10
            .rows(1).Font.Name = "Tahoma"
            .rows(1).Interior.ColorIndex = 36
            .Range("A1").Select 'Selects first cell to deselect the rest of the sheet.
        End With

If Dir(strYourPathAndFileNameHere) <> vbNullString Then Kill(strYourPathAndFileNameHere)End If
Click to expand...

for the above it works like a charm when the excel file is closed and is on my c drive. However if i have the spreadsheet open then it errors out highlighting my transferspreadsheet command. Is there a way to kill the spreadsheet even if its open?
 
It may be because your code isn't correct.

You have this:
Code: 
        With xlApp.ActiveSheet.Cells.Select
            .columns.autofit
            .rows.autofit
        End With

But it is supposed to be this:
Code: 
    With xlApp.ActiveSheet
       .Cells.Select
       .Cells.EntireColumn.AutoFit
       .Cells.EntireRow.AutoFit
     End With

You have too much in your WITH part.

As for the spreadsheet being open - NOPE you have to make sure it is closed. There is no way to delete it when it is open.
 
thank you Bob, but even with the correct code it doesnt formatt correctly. Here is what the code is... i think i maybe doing something really wrong here cuz this is very simple and its should not be that hard... any help you can provide will be great.

Code: 
For Each ctl In MyForm.Controls
    Select Case ctl.ControlType
    Case acCheckBox
        If ctl = True Then
            Set rst = db.OpenRecordset(ctl.Name)
            If Not rst.EOF Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""
                'DoCmd.OpenTable ctl.Name
            End If
            rst.Close
            Set rst = Nothing
        End If
    End Select
Next ctl
Dim strFileName As String
strFileName = Dir("C:\SCF Records.xls")
If strFileName = "SCF Records.xls" Then
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open(fileIn)
    Dim intCountofSheets, intCurrentSheet As Integer
    intCountofSheets = xlBook.sheets.Count
    intCurrentSheet = 1
    Do While intCurrentSheet <= intCountofSheets
    xlBook.Worksheets(intCurrentSheet).Activate
        With xlApp.ActiveSheet.UsedRange.Font
             .Name = "Tahoma"
             .Size = 10
        End With
        With xlApp.ActiveSheet
            .Cells.Select
            .Cells.EntireColumn.AutoFit
            .Cells.EntireRow.AutoFit
            .rows.Select
            .rows(1).Font.Bold = True
            .rows(1).Font.Size = 10
            .rows(1).Font.Name = "Tahoma"
            .rows(1).Interior.ColorIndex = 36
            .Range("A1").Select 'Selects first cell to deselect the rest of the sheet.
        End With
    intCurrentSheet = intCurrentSheet + 1
    Loop
    xlBook.Worksheets(1).Activate 'Returns the selection to the first worksheet.
    xlBook.Save
    xlBook.Close
    xlApp.Quit
    Set xlBook = Nothing
    Set xlApp = Nothing
Else
    MsgBox "No records to export"
End If
End Sub
 
Okay, for one I don't see how you are populating the fileIn variable so that might be good to see. Second, I modified your code a little to make it a little more compact and get the autofit into the right place (it needs to happen AFTER you set the font sizes) and you also were setting the entire row for the first row which formats everything to the entire end of the worksheet which will add a bunch of extra file size. Only do it for the used range.

Also, I would use Longs instead of Integers for the counts and sheet counts because if this is used in 2007 or 2010 there can be more than 255 columns and at least in 2003 and above you can have more than 255 worksheets.

Code: 
[SIZE=3][FONT=Times New Roman]Dim lngCountofSheets As Long[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim lngCurrentSheet As Long[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]Dim strFileName As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3]For Each ctl In MyForm.Controls[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Select Case ctl.ControlType[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Case acCheckBox[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        If ctl = True Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Set rst = db.OpenRecordset(ctl.Name)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            If Not rst.EOF Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, ""[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                'DoCmd.OpenTable ctl.Name[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            rst.Close[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Set rst = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End Select[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]Next ctl[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3]strFileName = Dir("C:\SCF Records.xls")[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3]If strFileName = "SCF Records.xls" Then[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Set xlApp = CreateObject("Excel.Application")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Set xlBook = xlApp.Workbooks.Open(fileIn)[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[SIZE=3][FONT=Times New Roman]    lngCountofSheets = xlBook.sheets.Count[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    lngCurrentSheet = 1[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3][/SIZE][/FONT] 
[SIZE=3][FONT=Times New Roman]    Do While lngCurrentSheet <= lngCountofSheets[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    xlBook.Worksheets(intCurrentSheet).Activate[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]        With xlApp.ActiveSheet.UsedRange[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]             .Font.Name = "Tahoma"[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]             .Font.Size = 10[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]            .rows.Select[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]            .rows(1).Font.Bold = True[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]            .rows(1).Font.Size = 10[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]            .rows(1).Font.Name = "Tahoma"[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]            .rows(1).Interior.ColorIndex = 36[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]            .Cells.Select[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]            .Cells.EntireColumn.AutoFit[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]            .Cells.EntireRow.AutoFit[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=red]        End With[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            .Range("A1").Select 'Selects first cell to deselect the rest of the sheet.[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    lngCurrentSheet = lngCurrentSheet + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Loop[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    xlBook.Worksheets(1).Activate 'Returns the selection to the first worksheet.[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    xlBook.Save[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    xlBook.Close[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    xlApp.Quit[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Set xlBook = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Set xlApp = Nothing[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]Else[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    MsgBox "No records to export"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End If[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]
 
THANK YOU Bob!!! that works really well. appreciate your help on this!!!
 
You must log in or register to reply here.

Similar threads

Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
1,825
Gasman
Gasman
zelarra821
List all song features
2
Replies
22
Views
2,009
zelarra821
zelarra821
L
VBA - How to save in csv one of multiple worksheets in excel file.
Replies
5
Views
1,356
CJ_London
C
R
Closing Excel from Access VBA
2 3
Replies
59
Views
5,613
ebs17
E
oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
1,772
oxicottin
oxicottin

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom