Another export to excel question

[PsYc0TiC]

I use automation code - opening Excel objects in VBA and manipulating those objects to set formatting - I set the column widths, insert lines, add formulas, etc.
I haven't found any other way to format an Excel workbook from Access.
-------------------------------------------------------------------------------------------------------------------


I understand that you might be getting frustrated, but a response like this could/will seriously reduce your chances getting an answer.
There are several people that have lashed out like this (not to me) - I no longer respond to them. Continue like this response and you will probably be in the same situation.

Everyone in the forum is a volunteer giving of their time and knowledge. Being rude/disrespectful does not help your situation.......

I totally understand... I guess I will ask elsewhere since there are no actual answers... just suggestions of methods but no actual code to show how that is accomplished. I was asking for someone to "volunteer" to edit my code to make it work since reading on the net has only gotten me so far.

Thank you very much for your responses but never mind.

[kd2017]

Also not a good idea (on any forum) to expect others to do the work for you...

[June7]

Your code can't 'be made to work'. You will need a different approach to accomplish your requirement. There are examples of Excel automation available. The Ken Snell site is a starting point. A quick Bing search on "Access vba Excel formatting" found https://www.mrexcel.com/forum/excel-...ccess-vba.html. Adapt code for your specific situation and when you encounter issue post question.

[PsYc0TiC]

lol... can't "be made to work"... well you are incorrect sir... editing the code is how you "make it work" and it did make it work. All done by me, the person sitting next to me and a very nice gentleman on another site where they didn't just vomit up the same links to the same sites that everyone else does. I read all of those and I still wasn't able to do it on my own. Telling someone to read after their first post pleaded saying they had been reading for days (remember... I read all those links that everyone was posting) is just lame. After studying the code that was written for me there is no way in any time frame I would have understood it from a "Go read and search Google" standpoint.

I never "expected" anyone to do the work for me but i did ask if someone would... if you didn't want to, simply don't reply.

Bye

Delete account please

[June7]

Perhaps you could share your solution. We might learn something new.

I am not a professional programmer. Learned Access/VBA as incidental duties in my job. I always found the referenced sites quite informative and helpful.

[kd2017]

Well color me perplexed and intrigued on a few different levels.

I'm also curious what the solution was.

[PsYc0TiC]

These sites might help you understand how it was solved.

http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

http://support.office.com/en-us/arti...7-70649E33BE4F

[PsYc0TiC]

Edit:

Undeserved code removed for people that just say "read"

[kd2017]

I use automation code - opening Excel objects in VBA and manipulating those objects to set formatting - I set the column widths, insert lines, add formulas, etc.
I haven't found any other way to format an Excel workbook from Access.

Thanks to arnelgp on another site.

Dim xlWB As Object
    Dim xlObj As Object
    Dim xlSheet As Object
    Dim lngRow As Long
    
    Set xlObj = CreateObject("Excel.Application")
    Set xlWB = xlObj.Workbooks.Open(strFileName, False, False)
    
    For Each xlSheet In xlWB.Worksheets
        
        With xlSheet
            
            .Activate
            lngRow = .Cells(.Rows.Count, 1).End(-4162).Row
            Debug.Print lngRow
            .Range("F1:F" & lngRow).Select
            xlObj.Selection.FormatConditions.Add Type:=2, Formula1:= _
                    "=TODAY()-F1>13"
            xlObj.Selection.FormatConditions(xlObj.Selection.FormatConditions.Count).SetFirstPriority
            With xlObj.Selection.FormatConditions(1).Interior
                .PatternColorIndex = -4105
                .Color = 255
                .TintAndShade = 0
            End With
            xlObj.Selection.FormatConditions(1).StopIfTrue = False
            .Range("A1:G1").Select
            With xlObj.Selection
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ReadingOrder = xlContext
                .MergeCells = False
                
                With .Font
                    .Name = "Calibri"
                    .FontStyle = "Bold"
                    .Size = 11
                End With
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .ColorIndex = xlAutomatic
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                .Borders(xlInsideHorizontal).LineStyle = xlNone
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.14996795556505
                    .PatternTintAndShade = 0
                End With
            End With
            .Columns("A:A").Select
            xlObj.Selection.ColumnWidth = 8.3
            .Columns("B:B").Select
            xlObj.Selection.ColumnWidth = 28.86
            .Columns("C:C").Select
            xlObj.Selection.ColumnWidth = 13.29
            .Columns("D:D").Select
            xlObj.Selection.ColumnWidth = 12.57
            .Columns("E:E").Select
            xlObj.Selection.ColumnWidth = 13.57
            .Columns("F:F").Select
            xlObj.Selection.ColumnWidth = 11
            .Columns("G:G").Select
            xlObj.Selection.ColumnWidth = 13.29
            .Range("A1").Select
            xlObj.ActiveWindow.FreezePanes = False


                        
        End With


    Next
    xlObj.Sheets(1).Activate
    xlWB.Close True
    Set xlSheet = Nothing
    Set xlWB = Nothing
    xlObj.Quit
    Set xlObj = Nothing


End Sub

I believe this is the automation code ssanfu was referring to, was it not?

[PsYc0TiC]

sure is... anybody actually help me with that? NO. I already knew automation code was what was needed... I lacked the skills to accomplish it hence why I asked if someone would help me with it... not tell me that it was needed... I knew it was needed.

As I said before... no actual help on this site

Delete my account please.

[kd2017]

Edit:

Undeserved code removed for people that just say "read"

For the curious who would like to "read":
https://access-programmers.co.uk/forums/showthread.php?t=296029
https://access-programmers.co.uk/for...d.php?t=296089

[PsYc0TiC]

For the curious who would like to "read":
https://access-programmers.co.uk/forums/showthread.php?t=296029
https://access-programmers.co.uk/for...d.php?t=296089

Yep... those links show you how somebody actually helps someone except they are on another site that believes in actual help. Go there for good help.

[kd2017]

I'm glad you found the answer you needed. Good luck with your project.