Pass through query results to matrix which is then exported to excel

M

mor

Registered User.
Local time
Tomorrow, 00:55
Joined
Jun 28, 2013
Messages
56
Hi all,

I have wrote some code which calls a query using querydefs and then pulls all the records into a table. This table is then exported to excel, however I seem to be having some trouble with the export and am finding it difficult to identify where the problem is coming from. When I run my code in step by step, more of than not the code passes fine and exports to excel. However, most of the time when I run the code as a whole without step by step, then the excel application will load but the workbook will not. The application then continues to close and the code completes without error? The code for the opening of the excel file is below.

The pause is a function i created to see if it was a problem of giving the exel application some time to load!

Code: 
Sub MiseEnForme1_Excel()
 
 Dim AppExcel As Excel.Application
 Dim WkbExcel As Excel.Workbook
 Dim WksExcel As Excel.Worksheet

Set AppExcel = New Excel.Application
AppExcel.Visible = True
Pause (5)
Set WkbExcel = Workbooks.Add
WkbExcel.Activate
Pause (5)
Sheets("Feuil1").Name = "DailyFees"
Sheets("DailyFees").Activate



Range("A1").Select

        With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
        End With
         
    
    Range("1:1").Select
    Selection.RowHeight = 50
        
    
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8282112
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("4:4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8282112
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Deal Id"
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "Brokerage Firm"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "Client Company"
    Range("D4").Select
    ActiveCell.FormulaR1C1 = "Product Purchased"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "Shares Purchased"
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "Management Fee %"
    Range("G4").Select
    ActiveCell.FormulaR1C1 = "Retro Fees %"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "Date de Valorisation"
    Range("I4").Select
    ActiveCell.FormulaR1C1 = "Valeur Liquidative"
    Range("J4").Select
    ActiveCell.FormulaR1C1 = "Days at VL"
    Range("L4").Select
    ActiveCell.FormulaR1C1 = "Daily Fees"
    
     Range("A4:Z4").Select
     Selection.RowHeight = 20
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    

End Sub

I should also add then when the matrix does export there are no problems at all!
 
Last edited:
Try the DoEvents instead of the Pause.
 
hi JHB, I've added the DoEvents as seen below, but I still have the opening/closing problem of the excel window without any error and no seeming pattern as to when the code run correctly or not.

Any ideas?

[\CODE]

Dim AppExcel As Excel.Application
Dim WkbExcel As Excel.Workbook
Dim WksExcel As Excel.Worksheet

Set AppExcel = New Excel.Application
AppExcel.Visible = True
DoEvents
Set WkbExcel = Workbooks.Add
DoEvents
WkbExcel.Activate

Sheets("Feuil1").Name = "DailyFees"
Sheets("DailyFees").Activate



Range("A1").Select

With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With


Range("1:1").Select
Selection.RowHeight = 50


With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 8282112
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Range("4:4").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 8282112
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Range("A4").Select
ActiveCell.FormulaR1C1 = "Deal Id"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Brokerage Firm"
Range("C4").Select
ActiveCell.FormulaR1C1 = "Client Company"
Range("D4").Select
ActiveCell.FormulaR1C1 = "Product Purchased"
Range("E4").Select
ActiveCell.FormulaR1C1 = "Shares Purchased"
Range("F4").Select
ActiveCell.FormulaR1C1 = "Management Fee %"
Range("G4").Select
ActiveCell.FormulaR1C1 = "Retro Fees %"
Range("H4").Select
ActiveCell.FormulaR1C1 = "Date de Valorisation"
Range("I4").Select
ActiveCell.FormulaR1C1 = "Valeur Liquidative"
Range("J4").Select
ActiveCell.FormulaR1C1 = "Days at VL"
Range("L4").Select
ActiveCell.FormulaR1C1 = "Daily Fees"

Range("A4:Z4").Select
Selection.RowHeight = 20
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With


End Sub

Sub MiseEnForme2_Excel()

Dim i As Long
i = 5

Do While Not IsEmpty(Cells(i + 1, 1))

If Cells(i, 1) <> Cells(i + 1, 1) Then
Cells(i + 1, 3).Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert

Cells(i + 2, 1).Activate
Rows(i + 2).Select
Selection.RowHeight = 20

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 8282112
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
End With

Cells(i + 2, 1).Select
ActiveCell.FormulaR1C1 = "Deal Id"
Cells(i + 2, 2).Select
ActiveCell.FormulaR1C1 = "Brokerage Firm"
Cells(i + 2, 3).Select
ActiveCell.FormulaR1C1 = "Client Company"
Cells(i + 2, 4).Select
ActiveCell.FormulaR1C1 = "Product Purchased"
Cells(i + 2, 5).Select
ActiveCell.FormulaR1C1 = "Shares Purchased"
Cells(i + 2, 6).Select
ActiveCell.FormulaR1C1 = "Management Fees"
Cells(i + 2, 7).Select
ActiveCell.FormulaR1C1 = "Retro Fees % Rate"
Cells(i + 2, 8).Select
ActiveCell.FormulaR1C1 = "Date de Valorisation"
Cells(i + 2, 9).Select
ActiveCell.FormulaR1C1 = "Valeur Liquidative"

Cells(i + 2, 10).Select
ActiveCell.FormulaR1C1 = "Days at VL"
Cells(i + 2, 12).Select
ActiveCell.FormulaR1C1 = "Daily Fees"

i = i + 3
Counter = Counter + 1
Else
i = i + 1
End If
Loop

Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit

Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Columns("A:A").Select
Selection.EntireColumn.Hidden = True

Range("A1:I1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

End Sub
[\CODE]
 
You are not cleaning up your applications after running this code which is leaving a hidden session of Excel running, that is causing this behaviour.

As well as there is an issue in your code that is (in part) creating this, on top of which the code could be beautified a bit.

Set AppExcel = New Excel.Application
AppExcel.Visible = True
DoEvents
Set WkbExcel = appExcel.Workbooks.Add

At the end of your code you need to "unset" all the excel stuff you have actually set...
Set AppExcel = Nothing
Set WkbExcel = Nothing
Set WksExcel = Nothing

To guarantee your code working always refer to the full excel path,
instead of range("A1").Select
Use wkbExcel.Range("A1").Select
Annoying but the only way (bar using a with constructtion like below) to guarantee the proper workings.

With wkbExcel
.Range("A1").Select
....
End With

Also you can fill cells directly instead of doing Range().Select, selection.formula = """
So instead of:
Range("A4").Select
ActiveCell.FormulaR1C1 = "Deal Id"
You can use
Range("A4") = "Deal Id"
This will make your code run a bit faster and a bit smoother


Lastly instead of something like
Sheets("Feuil1").Name = "DailyFees"
you are depending on the language settings of the client using the code to be (I think) a certain language if it is set to English it will be called "Sheet1" which will break your code.
Instead
Sheets(1).name = "DailyFees"
Will rename the first sheet, regardless of its name making you language independant.

I hope I didnt convolute your question to much and this helps you on your way
 
Namliam, that works great and is unbelievably helpful!

May I ask for some further help though? I have made the above modifications and now have the following issues:

The code now works alternately. Every other time that I execute the code, the run time error 1004 appears. "The method "Sheets" of the object _Global failed". Do you know why this code appears every other instance of running the code?

I have added the set "...." = nothing at the end of another sub which calls the matrix into Excel.

Code: 
Sub MiseEnForme1_Excel()
 
 Dim AppExcel As Excel.Application
 Dim WkbExcel As Excel.Workbook
 Dim WksExcel As Excel.Worksheet

Set AppExcel = New Excel.Application
AppExcel.Visible = True
DoEvents
Set WkbExcel = AppExcel.Workbooks.Add
WkbExcel.Activate

Sheets("Feuil1").Name = "DailyFees"
Sheets("DailyFees").Activate

Range("A1").Select

        With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
        End With
         
    
    Range("1:1").Select
    Selection.RowHeight = 50
        
    
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8282112
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("4:4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8282112
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("A4").Select = "Deal Id"
    Range("B4").Select = "Brokerage Firm"
    Range("C4").Select = "Client Company"
    Range("D4").Select = "Product Purchased"
    Range("E4").Select = "Shares Purchased"
    Range("F4").Select = "Management Fee %"
    Range("G4").Select = "Retro Fees %"
    Range("H4").Select = "Date de Valorisation"
    Range("I4").Select = "Valeur Liquidative"
    Range("J4").Select = "Days at VL"
    Range("L4").Select = "Daily Fees"
    
     Range("A4:Z4").Select
     Selection.RowHeight = 20
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
 
Like I said above
namliam said:
To guarantee your code working always refer to the full excel path,
instead of range("A1").Select
Use wkbExcel.Range("A1").Select
Annoying but the only way (bar using a with constructtion like below) to guarantee the proper workings.

With wkbExcel
.Range("A1").Select
....
End With
Click to expand...

But Perhaps I wasnt clear enough, each and every line doing anything in excel should be prefixed by this
 
With this code I get error 438, this property or method is not managed by this object.

Code debugger references

Wkb.Range("A1").Select.

Code below

Code: 
Option Compare Database

Sub MiseEnForme1_Excel()
 
 Dim AppExcel As Excel.Application
 Dim WkbExcel As Excel.Workbook
 Dim WksExcel As Excel.Worksheet

Set AppExcel = New Excel.Application
AppExcel.Visible = True
DoEvents
Set WkbExcel = AppExcel.Workbooks.Add
WkbExcel.Activate

Sheets(1).Name = "DailyFees"
Sheets("DailyFees").Activate

WkbExcel.Range("A1").Select

With WkbExcel
.Range("A1").Select
        
        With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
        End With
    
Range("1:1").Select
Selection.RowHeight = 50
        
    
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8282112
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("4:4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8282112
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("A4").Select = "Deal Id"
    Range("B4").Select = "Brokerage Firm"
    Range("C4").Select = "Client Company"
    Range("D4").Select = "Product Purchased"
    Range("E4").Select = "Shares Purchased"
    Range("F4").Select = "Management Fee %"
    Range("G4").Select = "Retro Fees %"
    Range("H4").Select = "Date de Valorisation"
    Range("I4").Select = "Valeur Liquidative"
    Range("J4").Select = "Days at VL"
    Range("L4").Select = "Daily Fees"
    
     Range("A4:Z4").Select
     Selection.RowHeight = 20
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End With

Set AppExcel = Nothing
Set WkbExcel = Nothing
Set WksExcel = Nothing

End Sub
 
Well range is a part of a sheet, not the workbook...

So you should set wksExcel = wkbexcel.Sheets("")
and use wksExcel for range functions, the key being you need to refer ultimately to the excel opbjects otherwize the access code doesnt always know what to do with it.....

Its stupid that it dont work, but it is one of the (many) quirks of interapplication coding.
 
Thanks so much for your help.. But I now seem to be getting run time error '91'.

Object variable or With block variable not set.

This occurs at line

WksExcel = WkbExcel.Sheets(1)

Can anyone help out as to why this error occurs and why it needs a With block?

Code: 
Sub MiseEnForme1_Excel()
 
 Dim AppExcel As Excel.Application
 Dim WkbExcel As Excel.Workbook
 Dim WksExcel As Excel.Worksheet

Set AppExcel = New Excel.Application
AppExcel.Visible = True
DoEvents
Set WkbExcel = AppExcel.Workbooks.Add
WkbExcel.Activate

WksExcel = WkbExcel.Sheets(1)
Sheets(1).Name = "DailyFees"
End With

Sheets("DailyFees").Activate

WksExcel.Range("A1").Select

       
        With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
        End With
    
Range("1:1").Select
Selection.RowHeight = 50
        
    
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8282112
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("4:4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 8282112
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("A4").Select = "Deal Id"
    Range("B4").Select = "Brokerage Firm"
    Range("C4").Select = "Client Company"
    Range("D4").Select = "Product Purchased"
    Range("E4").Select = "Shares Purchased"
    Range("F4").Select = "Management Fee %"
    Range("G4").Select = "Retro Fees %"
    Range("H4").Select = "Date de Valorisation"
    Range("I4").Select = "Valeur Liquidative"
    Range("J4").Select = "Days at VL"
    Range("L4").Select = "Daily Fees"
    
     Range("A4:Z4").Select
     Selection.RowHeight = 20
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With


End Sub

Thanks again for your help.
 
namliam said:
Well range is a part of a sheet, not the workbook...

So you should set wksExcel = wkbexcel.Sheets("")and use wksExcel for range functions, the key being you need to refer ultimately to the excel opbjects otherwize the access code doesnt always know what to do with it.....

Its stupid that it dont work, but it is one of the (many) quirks of interapplication coding.
Click to expand...

I probably wasnt explicit enough, you need the Set too...

Set WksExcel = WkbExcel.Sheets(1)
 
Ok I forgot to Set the wksExcel. Thanks everyone!
 
Thanks namliam! Final question I promise : )

Why do you call wkbexcel and not wksexcel when using the With block? I thought if you would want to select a range you would have to specify the worksheet!

namliam said:
To guarantee your code working always refer to the full excel path,
instead of range("A1").Select
Use wkbExcel.Range("A1").Select
Annoying but the only way (bar using a with constructtion like below) to guarantee the proper workings.

With wkbExcel
.Range("A1").Select
....
End With


I hope I didnt convolute your question to much and this helps you on your way
Click to expand...
 
my example was a quick copy/paste snip it to give you the idea of what I meant ...
Obviously I made the mistake of working with book and range... which instead obviously should be sheet and range
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Export search form data to excel
Replies
11
Views
989
oxicottin
oxicottin
D
Solved Formatting Excel File from Access
Replies
5
Views
1,039
Eugene-LS
Eugene-LS
Z
Attempts to make my code more efficient don't work
Replies
5
Views
556
Zedster
Z
B
Solved VBA code which exports data from Access to Excel and then loops through the Excel file
Replies
0
Views
731
BlueFllame
B
D
Join Expression Not Supported
Replies
13
Views
1,625
Drand
D

Users who are viewing this thread

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