Fomatting Excel in Access VB

[ChrisGow]

I have a complex excel macro that does a lot of formatting.

I have been able to get the macro copied into access and most commands working. I am having a huge problem with some other commands though.
Anything to do with setting borders is a problem, trying to set the horizontal allignment, setting row height, settign headers and footers are a few of the problems.

Does anyone have a sample where they do all this formatting through access or a link to somewhere that has a list of all the excel macro commands converted into access

Most commands are straight copies with the addition of object. infront of it but some will not work this way

 

[Bat17]

see if there is anything in here that helps

Sub DelXlSheet()
Dim xlapp As Excel.Application
Dim wk As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlapp = New Excel.Application
Set wk = xlapp.Workbooks.Open("C:\MyFile.xls")
xlapp.DisplayAlerts = False
    wk.Sheets("Sheet2").Delete
xlapp.DisplayAlerts = True
xlapp.Worksheets.Add.Move After:=xlapp.Worksheets(xlapp.Worksheets.Count)
xlapp.Sheets(xlapp.Worksheets.Count).Name = "newSheet"
wk.Sheets("newSheet").Range("A1") = "First Header"
wk.Sheets("newSheet").Range("B1") = "Second Header"
wk.Sheets("newSheet").Range("C1") = "Third Header"
With wk.Sheets("newSheet").Range("A1:C1")
    .Font.Bold = True
    .Font.ColorIndex = 3
    .Interior.ColorIndex = 37
    .Interior.Pattern = xlSolid
    .HorizontalAlignment = xlCenter
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlMedium
    .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeTop).ColorIndex = xlAutomatic
    
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlMedium
    .Borders(xlEdgeRight).ColorIndex = xlAutomatic
    

    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideVertical).Weight = xlThin
    .Borders(xlInsideVertical).ColorIndex = xlAutomatic
    
    

End With
wk.Sheets("newSheet").Columns("A:C").EntireColumn.AutoFit
        

'Save with current name
wk.Save
xlapp.Visible = True
'wk.Close
xlapp.UserControl = True
'xlApp.Quit
Set wk = Nothing
Set xlapp = Nothing
End Sub

Shout back if you need more specific help

Peter

 

[ChrisGow]

Heres what I have

xl.ActiveSheet.Columns("A:G").HorizontalAlignment = xlCenter

and it gives me unable to set the horizontal alignment property of the range class

 

[reclusivemonkey]

Try specifying the sheet, rather than using "ActiveSheet".

 

[ChrisGow]

Ok I have tried every version of using your code, including copying and paste your whole code and it has come to my attention that we are running different versions of Access or Excel or both. The dim xlapp as Excel.Application gives me a user defined type not defined.

I have tried using parts of your code and modifying it to adapt to my code however it still runs into the problem that it cant do the horizontal align or anything else.

ReclusiveMonkey - have tried doing that aswell

 

[chriswies]

Try this...

Your macro will work if you use Worksheets instead of Sheets.

I shortened your code (and did not work with your deletion and sorting) and used your xlSheet Object-Variable.
Christoph

Sub DelXlSheet()
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    Set xlWb = xlApp.Workbooks.Add
    Set xlSheet = xlWb.Worksheets.Add(After:=xlWb.Worksheets(xlApp.Worksheets.Count))
    xlSheet.Name = "newSheet"
    
    With xlSheet
        .Range("A1") = "First Header"
        .Range("B1") = "Second Header"
        .Range("C1") = "Third Header"
    End With
    
    With xlSheet.UsedRange
        .Font.Bold = True
        .Font.ColorIndex = 3
        .Interior.ColorIndex = 37
        .HorizontalAlignment = xlCenter
        .BorderAround xlContinuous, xlMedium
        .EntireColumn.AutoFit
    End With
        

    'Save with current name
    xlWb.Save
    xlApp.Visible = True
    'xlWb.Close
    xlApp.UserControl = True
    'xlApp.Quit
    Set xlSheet = nothing
    Set xlWb = Nothing
    Set xlApp = Nothing
End Sub

 

[chriswies]

The dim xlapp as Excel.Application gives me a user defined type not defined.
ReclusiveMonkey - have tried doing that aswell

ChrisGow, you had not set the Microsoft Excel Reference.
Christoph

 

[Bat17]

if you want to use late binding you will need to look up the values for all of the XL constants such as 'xlCenter' and replace them in your code.

HTH

Peter

 

[ChrisGow]

Automatically answering questions

When I am programming in vb for access to control excel. When I save and close the file it asks if I want to overwrite the file that exists, which I do. I want to be able to automatically answer this yes.

what is the code to do this I cannot find it .

 

[tkpstock]

When I am programming in vb for access to control excel. When I save and close the file it asks if I want to overwrite the file that exists, which I do. I want to be able to automatically answer this yes.

what is the code to do this I cannot find it .

Excel.Application.DisplayAlerts = False

HTH

 

[ChrisGow]

I want to be able to answer yes or no to the overwrite question in my code.....

 

[tkpstock]

If you want to save the workbook, then:

xlWB.Save
xlApp.Quit

If you don't want to save it:

xlWB.Saved = True
xlApp.Quit

HTH

 

[ChrisGow]

Will this work when Im using the save as function

 

[tkpstock]

Will this work when Im using the save as function

Do you want the user interaction? Or do you want to be able to set the filename in your code as well?

Do
    fName = Application.GetSaveAsFilename
Loop Until fName <> False
xlWB.SaveAs Filename:=fName