Exporting to Excel (Formatting Issues) (1 Viewer)

X

xoail

New member
Local time
Today, 00:02
Joined
Sep 8, 2008
Messages
1
Hi all,

I've been researching a lot on this, its silly but still I couldnt find the right solution that has worked for me.
First, I am using access as GUI, MS SQL as db and Excel for reporting. I am trying to export the reports in excel and in the process format the cells and do basic calculations (sum of columns etc.)

But every time I try to use some code for doing this, I keep ending up with errors like "object required" or "unable to set the horizontalalignment property of the range class"

Here's what my code looks like right now for summing up column c (which returns object required on 2nd line):
Dim Lr As Long
Lr = Cells(Rows.Count, "J").End(xlUp).Row + 1
Cells(Lr, "j").Formula = "=SUM(J1:J" & Lr - 1 & ")"

Heres the code for simply aligning the column to the center:
xl.ActiveSheet.Columns("B").HorizontalAlignment = xlCenter

Any help highly appreciated.

Thanks!
 
D

dark11984

Registered User.
Local time
Today, 17:02
Joined
Mar 3, 2008
Messages
129
Hi just wondering if you ever got this working? I'm having the same type of issues on horizontalalignment.

Cheers
 
boblarson

boblarson

Smeghead
Local time
Today, 00:02
Joined
Jan 12, 2001
Messages
32,059
dark11984 said:
Hi just wondering if you ever got this working? I'm having the same type of issues on horizontalalignment.

Cheers
Click to expand...

What kind of issues? What is the code you are using?
 
D

dark11984

Registered User.
Local time
Today, 17:02
Joined
Mar 3, 2008
Messages
129
Hi Sorry for the late response...

i think i've fixed the horizontal allignment issue, but I'm having a number of issues with the code, This particular one is getting an error at .TintAndShade = 0. The error reads "Method or data member not found".


Code: 
objexcelapp.Range("A2:B3").Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With

I'm only new to excel automation but I think i read somewhere that it has to do with the use of "selection"?
 
boblarson

boblarson

Smeghead
Local time
Today, 00:02
Joined
Jan 12, 2001
Messages
32,059
This:

With Selection.Borders(xlEdgeLeft)

Should be this:

With objexcelapp.Selection.Borders(xlEdgeLeft)
 
D

dark11984

Registered User.
Local time
Today, 17:02
Joined
Mar 3, 2008
Messages
129
Thanks Bob, a silly mistake to miss.

The code is now working, but if i run the code a second time i get an error of "Method 'Columns' of object '_Global' failed" on:
Code: 
j = objexcelapp.WorksheetFunction.CountIf(Columns("B:B"), "Core Fleet")

If i end the code and run again it works.
 
boblarson

boblarson

Smeghead
Local time
Today, 00:02
Joined
Jan 12, 2001
Messages
32,059
Make sure you don't have any residual Excel.EXE processes running (in your Task Manager). See here for how that can happen.
 
D

dark11984

Registered User.
Local time
Today, 17:02
Joined
Mar 3, 2008
Messages
129
Yeah thats the problem, it leaves an excel.exe open in the processes after the first time. I've read the article too, but im still a bit lost.

My code opens excel with:

Code: 
    Set objexcelapp = New Excel.Application
    Set objexcelwb = objexcelapp.Workbooks.Open _
    ("C:\data\Projects\RateCard.xls")
    objexcelapp.Visible = True

Do i have to put ActiveSheet in front of each code or am i right just to leave as
Code: 
objexcelapp.Rows("1:1").Select

i'm wondering if its because i'm not closing excel at the end of the code? (I want to leave the work book open for review and then close manually)
 
boblarson

boblarson

Smeghead
Local time
Today, 00:02
Joined
Jan 12, 2001
Messages
32,059
dark11984 said:
Do i have to put ActiveSheet in front of each code or am i right just to leave as
Code: 
objexcelapp.Rows("1:1").Select
Click to expand...
I don't know for sure. If it works then great. If not, then you probably need it.

i'm wondering if its because i'm not closing excel at the end of the code? (I want to leave the work book open for review and then close manually)
Click to expand...

Post the entire code so we can comb through it to see if anything jumps out. But if you want to leave it open for review, add this to the end of your code:
Code: 
objexcelapp.UserControl = True
 
Set objexcelapp = Nothing

That disconnects the object from the application and allows you to kill the variable.
 
D

dark11984

Registered User.
Local time
Today, 17:02
Joined
Mar 3, 2008
Messages
129
Code: 
Private Sub CmdRateCardExport_Click()
'On Error GoTo Err_CmdRateCardExport_Click
    Dim CMS As String
    Dim Site As String
    Dim x As Integer
    Dim y As Integer
    Dim j As Long
    Dim k As Long
    Dim objexcelapp As Excel.Application
    Dim objexcelwb As Excel.Workbook
 
    CMS = Forms!frmratecardentry!CboCMSRef
    Site = Forms!frmratecardentry!cboSite
 
    'Delete existing RateCard.xls file
If Dir("C:\data\Projects\RateCard.xls") <> "" Then
    Kill ("C:\data\Projects\RateCard.xls")
Else
 
    'Export QryRateCardExport from Rate Card Database
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QryRateCardExport", "C:\data\Projects\RateCard.xls", True, CMS & "-" & Site
 
    Set objexcelapp = New Excel.Application
    Set objexcelwb = objexcelapp.Workbooks.Open _
    ("C:\data\Projects\RateCard.xls")
    objexcelapp.Visible = True
 
    'Insert 4 rows
    objexcelapp.Rows("1:1").Select
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    'Insert 2 columns
    objexcelapp.Columns("A:A").Select
    objexcelapp.Selection.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    objexcelapp.Selection.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 
    'Copy Contract # & Site to header
    objexcelapp.Range("C5").Select
    objexcelapp.Selection.copy
    objexcelapp.Range("A2").Select
    objexcelapp.activesheet.paste
    objexcelapp.Application.CutCopyMode = False
    objexcelapp.Range("D5").Select
    objexcelapp.Selection.copy
    objexcelapp.Range("A3").Select
    objexcelapp.activesheet.paste
    objexcelapp.Application.CutCopyMode = False
    objexcelapp.Range("C6").Select
    objexcelapp.Selection.copy
    objexcelapp.Range("E2").Select
    objexcelapp.activesheet.paste
    objexcelapp.Application.CutCopyMode = False
    objexcelapp.Range("D6").Select
    objexcelapp.Selection.copy
    objexcelapp.Range("E3").Select
    objexcelapp.activesheet.paste
    objexcelapp.Application.CutCopyMode = False
 
    'Delete Contract # and site columns
    objexcelapp.Columns("B:D").Select
    objexcelapp.Selection.Delete shift:=xlToLeft
 
    'Delete RateCardType Heading
    objexcelapp.Range("b5").Clear
 
    'Make headings bold
    objexcelapp.Range("A2:B3").Select
    objexcelapp.Selection.Font.Bold = True
    objexcelapp.Selection.Font.ColorIndex = 2
    objexcelapp.Rows("5:5").Select
    objexcelapp.Selection.Font.Bold = True
    objexcelapp.Selection.Font.ColorIndex = 2
 
    j = objexcelapp.WorksheetFunction.CountIf(Columns("B:B"), "Core Fleet")
    k = objexcelapp.WorksheetFunction.CountIf(Columns("B:B"), "As Required")
 
    'Centre UM & Rate
    objexcelapp.Columns("d:e").Select
    With objexcelapp.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
 
    'Color background Blue
    objexcelapp.Cells.Select
    With objexcelapp.Selection
        .Interior.Color = 12611584
        .Font.Name = "arial"
    End With
 
    'Colour table background yellow & white
    x = 2
    For y = 6 To (j + k + 5)
        objexcelapp.Cells(y, x).Select
        With objexcelapp.Selection
            .Interior.ColorIndex = 1
            .Font.ColorIndex = 2
        End With
 
        objexcelapp.Cells(y, x + 1).Select
        With objexcelapp.Selection
            .Interior.ColorIndex = 36
        End With
 
        objexcelapp.Cells(y, x + 2).Select
        With objexcelapp.Selection
            .Interior.ColorIndex = 2
        End With
        objexcelapp.Cells(y, x + 3).Select
        With objexcelapp.Selection
            .Interior.ColorIndex = 2
        End With
    Next y
 
    'Colour headings black
    objexcelapp.Range("A2:B3").Select
    With objexcelapp.Selection
        .Interior.ColorIndex = 1
    End With
    objexcelapp.Range("B5:E5").Select
    With objexcelapp.Selection
        .Interior.ColorIndex = 1
    End With
 
    objexcelapp.Range("B6:E6").Offset(j, 0).Select
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    objexcelapp.Range("B6:E6").Offset(j, 0).Select
    With objexcelapp.Selection
        .Interior.Color = 12611584
    End With
 
    'Borders
    objexcelapp.Range("A2:B3").Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With
 
    objexcelapp.Range("B5:E5").Resize(j + 1, 4).Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = 1
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = 1
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = 1
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = 1
        .TintAndShade = 0
        .Weight = xlMedium
    End With
 
    objexcelapp.Range("b5:e5").Offset(j + 2, 0).Select
    objexcelapp.Selection.Resize(k, 4).Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = 1
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = 1
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = 1
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = 1
        .TintAndShade = 0
        .Weight = xlMedium
    End With
 
    'Column Borders
    objexcelapp.Range("B5").Resize(j + 1, 1).Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    objexcelapp.Selection.Borders(xlInsideVertical).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    objexcelapp.Selection.Offset(0, 1).Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    objexcelapp.Selection.Borders(xlInsideVertical).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    objexcelapp.Selection.Offset(0, 1).Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    objexcelapp.Selection.Borders(xlInsideVertical).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    objexcelapp.Range("B5:e5").Offset(j + 2, 0).Select
    objexcelapp.Selection.Resize(k, 1).Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    objexcelapp.Selection.Borders(xlInsideVertical).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    objexcelapp.Selection.Offset(0, 1).Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    objexcelapp.Selection.Borders(xlInsideVertical).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    objexcelapp.Selection.Offset(0, 1).Select
    objexcelapp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With objexcelapp.Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With objexcelapp.Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16777215
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With objexcelapp.Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    objexcelapp.Selection.Borders(xlInsideVertical).LineStyle = xlNone
    objexcelapp.Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
    'Merge Rate Card Type
    objexcelapp.Range("B6").Resize(j, 1).Select
    With objexcelapp.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    objexcelapp.Selection.Merge
    With objexcelapp.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    objexcelapp.Selection.Font.Bold = True
 
    objexcelapp.Range("B6").Offset(2, 0).Select
    objexcelapp.Selection.Resize(k, 1).Select
    With objexcelapp.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    objexcelapp.Selection.Merge
    With objexcelapp.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    objexcelapp.Selection.Font.Bold = True
 
    'Change rate to currency
    objexcelapp.Range("e6").Resize(j, 1).Select
    objexcelapp.Selection.Style = "Currency"
 
    objexcelapp.Range("e6").Offset(j + 1).Select
    objexcelapp.Selection.Resize(k, 1).Select
    objexcelapp.Selection.Style = "Currency"
    objexcelapp.Columns("A:E").entirecolumn.AutoFit
 
    objexcelapp.UserControl = True
    Set objexcelapp = Nothing
 
 
End If
 
'Exit_CmdRateCardExport_Click:
'    Exit Sub
'Err_CmdRateCardExport_Click:
'    MsgBox Err.Description
'    Resume Exit_CmdRateCardExport_Click
 
End Sub
 
boblarson

boblarson

Smeghead
Local time
Today, 00:02
Joined
Jan 12, 2001
Messages
32,059
Well, I'm not seeing any disconnects at this point.

But I do see some things which could be improved upon.

Like this:
Code: 
    'Copy Contract # & Site to header
    objexcelapp.Range("C5").Select
    objexcelapp.Selection.copy
    objexcelapp.Range("A2").Select
    objexcelapp.activesheet.paste
    objexcelapp.Application.CutCopyMode = False
    objexcelapp.Range("D5").Select
    objexcelapp.Selection.copy
    objexcelapp.Range("A3").Select
    objexcelapp.activesheet.paste
    objexcelapp.Application.CutCopyMode = False
    objexcelapp.Range("C6").Select
    objexcelapp.Selection.copy
    objexcelapp.Range("E2").Select
    objexcelapp.activesheet.paste
    objexcelapp.Application.CutCopyMode = False
    objexcelapp.Range("D6").Select
    objexcelapp.Selection.copy
    objexcelapp.Range("E3").Select
    objexcelapp.activesheet.paste
    objexcelapp.Application.CutCopyMode = False

Can be done like this:
Code: 
    'Copy Contract # & Site to header
    objexcelapp.Range("A2").Value = objexcelapp.Range("C5").Value
    objexcelapp.Range("A3").Value = objexcelapp.Range("D5").Value
    objexcelapp.Range("E2").Value = objexcelapp.Range("C6").Value
    objexcelapp.Range("E3").Value = objexcelapp.Range("D6").Value
No need to select, copy and then paste.

Also this:
Code: 
    'Insert 4 rows
    objexcelapp.Rows("1:1").Select
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Can be replaced by this:
Code: 
    'Insert 4 rows
    objexcelapp.Rows("1:[B][COLOR=red]4[/COLOR][/B]").Select
    objexcelapp.Selection.Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

And the Columns:
Code: 
    'Insert 2 columns
    objexcelapp.Columns("A:[COLOR=red][B]B[/B][/COLOR]").Select
    objexcelapp.Selection.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 
D

dark11984

Registered User.
Local time
Today, 17:02
Joined
Mar 3, 2008
Messages
129
ok thanks bob, have made those changes. I'm still trying to figure out where i am going wrong though. Thanks for your help.
 
D

dark11984

Registered User.
Local time
Today, 17:02
Joined
Mar 3, 2008
Messages
129
Got it working...

Changed
Code: 
j = objexcelapp.WorksheetFunction.CountIf(Columns("B:B"), "Core Fleet")
k = objexcelapp.WorksheetFunction.CountIf(Columns("B:B"), "As Required")

to
Code: 
j = objexcelapp.WorksheetFunction.CountIf(objexcelsheet.Columns("B:B"), "Core Fleet")
k = objexcelapp.WorksheetFunction.CountIf(objexcelsheet.Columns("B:B"), "As Required")
 
You must log in or register to reply here.

Similar threads

R
Updating Excel spreadsheet from within the Access
Replies
8
Views
457
CJ_London
C
C
Solved i want to change this code to merge two excel files to one sheet side by side.
Replies
2
Views
292
camiramzi
C
M
Query export to Excel through button
2 3
Replies
42
Views
1,027
Gasman
Gasman
oxicottin
Solved Export to Excel error '1004' With time format
Replies
9
Views
301
ebs17
E
MattBaldry
Solved Export and Format Excel
Replies
7
Views
640
Gasman
Gasman

Users who are viewing this thread

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