Solved: MS Access - Export to excel and change cell font

[boneKrusher]

Hi All,

I am trying to export some tables to excel and change the font on the activesheet range(A1:C1) to bold, but I cant seem to figure it out. The tables export, but I get an error when trying to set ws = activesheet. " Run-time error'91': Object variable or with block variable not set". Any help would be great.


Dim sfile As String, sThisMDB As String, sOpen As String
Dim stable As String
Dim stable2 As String
Dim sfile1 As String
Dim wb As Workbook
Dim ws As Worksheet
Dim fullname As String
stable = "XMEL"
stable2 = "XXMEL"
stable3 = "Notices"
Const q As String * 1 = """"
sThisMDB = CurrentDb.name
sfile = Left(sThisMDB, InStrRev(sThisMDB, "\")) & stable & ".xls"
If Dir(sfile) <> "" Then Kill sfile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stable, sfile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stable2, sfile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stable3, sfile, True
sOpen = "excel.exe " & q & sfile & q
Shell sOpen, vbNormalFocus
Set wb = ActiveWorkbook
Set ws = ActiveSheet.Range("A1:C1") ' ***Error HERE

With ws

Font.Bold = True
End With

Set wb = Nothing

[XLGibbs]

Set ws = wb.Sheets(1)
With ws
Range("A1:C1").Font.Bold = True
End with


Assumes the workbook has 1 sheet, or the sheet you intend is the first sheet in the workbook..

Problem was you were setting a range object to a worksheet variable. Set the worksheet first, then use that as reference to define the range and changes...

[boneKrusher]

Hi XLGibbs,

Thank you for the help. I am still recieving the error. Maybe i am missunderstanding the problem. here is what I have so far:


Dim sfile As String, sThisMDB As String, sOpen As String
Dim stable As String
Dim stable2 As String
Dim sfile1 As String
Dim wb As Workbook
Dim ws As Worksheet
Dim fullname As String

stable = "xxxMEL"
stable2 = "xxxMEL"
stable3 = "Notices"
Const q As String * 1 = """"
sThisMDB = CurrentDb.name
sfile = Left(sThisMDB, InStrRev(sThisMDB, "\")) & stable & ".xls"
If Dir(sfile) <> "" Then Kill sfile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stable, sfile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stable2, sfile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stable3, sfile, True
sOpen = "excel.exe " & q & sfile & q
Shell sOpen, vbNormalFocus
Set wb = ActiveWorkbook
Set ws = wb.Sheets(1)
With ws

Range("A1:C1").Font.Bold = True
End With
Set wb = Nothing
Set ws = Nothing

[XLGibbs]

Dunno.

I normally wouldn't use the shell command to open excel.exe and the subsequent filename..

If the excel file is actually opening...

Are you actually getting that object error on the Set statements again? or is it a different error now?

[boneKrusher]

Hi,

Its the same error on line:

ws=wb.sheets(1)

I think I will re-write the code using the excel application object (e.g. myexcel as excel.application). Mybe use a record set to loop through the records....

thoughts?

[boneKrusher]

Ok, I changed the code. It works great the 1st attempt but on the 2nd try only one sheet is displayed (Run-time error '1004' Mthod 'sheets' of object '_global' failed". The next try I get an error that I cant rename the worksheet. On the fourth try it works again (all tables are exported and worksheets are created). In my code do I shut down all instances of Excel? I thought I did. What am I missing?


Dim myexcel As Excel.Application
Dim Trst As DAO.Recordset
Dim rst As DAO.Recordset
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim StartRange As Excel.Range
Dim strConn As String
Dim i As Integer
Dim f As Variant

Set myexcel = New Excel.Application

Set Trst = CurrentDb.OpenRecordset("tables")

Set wbk = myexcel.Workbooks.Add
Set wks = Sheets.Add
Set wks = wbk.ActiveSheet

While Not Trst.EOF

Set rst = CurrentDb.OpenRecordset(Trst![tname])

' make the Excel application window visible
myexcel.Visible = True

i = 1

' Create the Column Headings in cells
With rst
For Each f In .Fields
With wks
.Cells(1, i).Value = f.name

i = i + 1
End With
Next
End With

' specify the cell range that will receive the data (A2)
Set StartRange = wks.Cells(2, 1)

' copy the records from the recordset
StartRange.CopyFromRecordset rst

wks.Columns.AutoFit
wks.Range("A1:C1").AutoFilter
wks.Range("A1:C1").Font.Bold = True
wks.Range("A1:C1").HorizontalAlignment = xlCenter

wks.name = Trst![tname]
'Set wks = Sheets(2)
rst.Close
Set rst = Nothing
Set wks = Sheets.Add
Trst.MoveNext

Wend

wks.Delete

Set rst = Nothing
Set wkb = Nothing
Set wks = Nothing
Set Trst = Nothing
Set myexcel = Nothing

[XLGibbs]

you probably want to have some error trapping in there.
.

I think ONE instance of excel is plenty, you can add workbooks to that one instance...

The problem is your wbk object. Every reference to it might be trying to add a workbook to a new instance of excel. Try this change and see if it sticks with one instance of excel, instead of potentially more


Dim wbkNew as Excel.Workbook
Set wbkNew = myexcel.Workbooks.add
Set wbk = ActiveWorkbook
wbk.Sheets.Add
Set wks = wbk.ActiveSheet
This line

wbk = myexcel.workbooks.add

always refers to that.

So this line:

Set wks = Sheets.Add

would add a sheet (to which instance/workbook?)

[boneKrusher]

Thanks XLgibbs,

I tried your sugestions, but it doesnt help. I only want one instance of the workbook. After the procedure, I want to quit that instance all together.

I know its not quiting because I rebooted my computer. On shut down I revieved about 15 dialogs asking do I want to save workbook5, 6 etc...

Ahhhhhh

[XLGibbs]

Sub test()


Dim objExcelApp As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet

Set objExcelApp = New Excel.Application 'sets object as new instance of excel
Set objWorkbook = objExcelApp.Workbooks.Add
Set objWorksheet = objWorkbook.ActiveSheet

objWorksheet.Range("A1") = "Test"

objWorksheet.Activate

objWorkbook.Close
objExcelApp.Quit
End Sub


The above works for me, one instance, instance closes...so I would do this:


Dim myexcel As Excel.Application
Dim Trst As DAO.Recordset
Dim rst As DAO.Recordset
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim StartRange As Excel.Range
Dim strConn As String
Dim i As Integer
Dim f As Variant

Set myexcel = New Excel.Application
myexcel.Visible = True ' make the Excel application window visible

On error goto Endearly 'end early goes to the spot where we kill the excel and clear the variable

Set Trst = CurrentDb.OpenRecordset("tables")

Set wbk = myexcel.Workbooks.Add
Set wks = ActiveSheet

While Not Trst.EOF
Set rst = CurrentDb.OpenRecordset(Trst![tname])
i = 1
' Create the Column Headings in cells
With rst
For Each f In .Fields
With wks
.Cells(1, i).Value = f.name
i = i + 1
End With
Next
End With

' specify the cell range that will receive the data (A2)
Set StartRange = wks.Cells(2, 1)

' copy the records from the recordset
StartRange.CopyFromRecordset rst

wks.Columns.AutoFit
wks.Range("A1:C1").AutoFilter
wks.Range("A1:C1").Font.Bold = True
wks.Range("A1:C1").HorizontalAlignment = xlCenter

wks.name = Trst![tname]
'Set wks = Sheets(2)
rst.Close
Set rst = Nothing
Set wks = Sheets.Add ' add a sheet after closing the recordset?
Trst.MoveNext 'move to next record after closing the recordset?

Wend

wbk.Close False 'set to true, with filename to save changes..

wks.Delete 'why is this here?


Set rst = Nothing
Set wkb = Nothing
Set wks = Nothing
Set Trst = Nothing
End early:
myexcel.quit 'kill the instance of excel
Set myexcel = Nothing

Which would seem to work(untested). Appears the extra line for the added worksheet you add was mucking things up. I glazed over it before, but when you add a workbook, it has at least one sheet by default..which is also the active sheet by default.

You also never closed/quit the application itself. Setting it to nothing doesn't do it by itself. Another glaze over job..sorry..

Anyways, those tweaks should help

I have had similar issues with some DTS packages that I script out some Excel template updating for reports. If for some reason the script fails, it left an instance of excel in the background. The on error message should take it to the set

[boneKrusher]

XLGibbs,

I cant thank you enough for your time and effort.

The code you posted dumps out on the error, but also seems to not kill excel. Its very strange. Your code makes sense.

So I went back to your orginal suggestion, and you were right. I was not delcaring what instance to assign a new work sheet.


Dim myexcel As Excel.Application
Dim Trst As DAO.Recordset
Dim rst As DAO.Recordset
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim StartRange As Excel.Range
Dim strConn As String
Dim i As Integer
Dim f As Variant

Set myexcel = New Excel.Application

myexcel.Visible = True ' make the Excel application window visible

Set Trst = CurrentDb.OpenRecordset("tables")

Set wbk = myexcel.Workbooks.Add
Set wks = wbk.ActiveSheet
'Set wks = ActiveSheet

While Not Trst.EOF

If wks Is Nothing Then
Set wks = wbk.ActiveSheet
Else
Set wks = Nothing
Set wks = wbk.ActiveSheet
'Set wks = Sheets.Add
End If


Set rst = CurrentDb.OpenRecordset(Trst![tname])
i = 1
' Create the Column Headings in cells
With rst

For Each f In .Fields
With wks

.Cells(1, i).Value = f.name
i = i + 1
End With
Next
End With

' specify the cell range that will receive the data (A2)
Set StartRange = wks.Cells(2, 1)

' copy the records from the recordset
StartRange.CopyFromRecordset rst

wks.Columns.AutoFit
wks.Range("A1:C1").AutoFilter
wks.Range("A1:C1").Font.Bold = True
wks.Range("A1:C1").HorizontalAlignment = xlCenter

wks.name = Trst![tname]

rst.Close

Set wks = wbk.Sheets.Add ' add a sheet after closing the recordset
Trst.MoveNext

Wend

wbk.Close True 'set to true, with filename to save changes..

wks.Delete 'why is this here?

Set rst = Nothing
Set wkb = Nothing
Set wks = Nothing
Set Trst = Nothing

Set myexcel = Nothing

funny...

I will marked this as solved, as you were very helpful in solving my problem. The real problem was in:

Set wks = wbk.Sheets.Add

thanks

Bones

[XLGibbs]

You still need

myexcel.quit

in your last posted code. Without that...the instance stays running even with the workbook getting closed.

Setting the variable = nothing only pertains the variable assignment in the VB memory... doesn't actually do anything with instance of excel.

[boneKrusher]

Thanks.

I was leaving it open if the user wanted to make any changes to the excel file. Wouldnt excel quit when the user closes the app?

[XLGibbs]

Not at all.

When you open excel using an icon it defaults to open new file, but when you create an instance, you can close the file without quiting the app. It is the same as hitting the upper right X to close a file only, but you still can see the main app window..