By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,209 Members | 1,100 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,209 IT Pros & Developers. It's quick & easy.

Export Access table to Excel and format wksht using macro

P: n/a
I have a temp table with one row of data that I need to export into
Excel. I created the export to create the xls file as follows ...

Dim FileName
FileName = [Forms]![frm_Regional ELC]![txtItemDescription]

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_delete FCL"
DoCmd.OpenQuery "qry_temp FCL"

DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, , _
"tbl_temp FCL", _
FileName, True

This works great to create the file in my docs.

Now I need to open and format the wksheet created. I created a macro
using the 'record' function in excel.

Here is an example of a pc of the code recorded ...

Application.CutCopyMode = False
Selection.Cut Destination:=Range("A7")
Range("A2").Select
Selection.Cut Destination:=Range("B7")
Range("A8").Select
ActiveCell.FormulaR1C1 = "Season"
Range("B1").Select
Selection.Cut Destination:=Range("A9")
Range("B2").Select
Selection.Cut Destination:=Range("B9")
Range("C1").Select

How can I open the new file and run the formatting macro from access
immediately after I export the table?

Any help would be appreciatted.

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Instead of putting the macro in Excel, I do this in Access. Try this
(air code, pulled from
some code I have used that works with a few changes):

Public Sub SendDataToExcelAndFormat()
Dim strFile as String
'Your code begins here
strFile = [Forms]![frm_Regional ELC]![txtItemDescription]
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_delete FCL"
DoCmd.OpenQuery "qry_temp FCL"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, , _
"tbl_temp FCL", _
FileName, True
'Your code ends here
Call FormatDataInExcel strFile
End Sub

Private Sub FormatDataInExcel(strExcelWorkbook As String)
On Error Goto Err_FormatDataInExcel
Dim exl as Object
'Attempt to assign an Excel application that already
'exists to object variable
Set exl = GetObject(,"Excel.Application")
'Open the workbook to format
exl.Workbooks.Open Filename:=strExcelWorkbook

'Your recorded macro code begins here

'Your recorded macro code code ends here

'Save changes
exl.ActiveWorkbook.Save
'Show the Excel application
exl.Application.Visible = True
Exit_FormatDataInExcel:
On Error Resume Next
Set exl = Nothing
Exit Sub
Err_FormatDataInExcel:
With err
Select Case .Number
Case 429
'Excel object does not exist, create it
Set exl = CreateObject("Excel.Application")
Resume Next
Case Else
Msgbox "An unhandled exception has occurred." & vbCrLf _
"Error Number: " & .Number & vbCrLf _
"Error Description: " & .Description, _
vbCritical
End Select
End With
Resume Exit_FormatDataInExcel
End Sub

Post back if you need clarification or get an error.

Johnny

Nov 13 '05 #2

P: n/a
Excellent - thank you for the help Johnny. I entered the code as you
suggested, but now I receive an error with the actual
transferspreadsheet function. "run time error '13': Type mismatch"

This is what I have entered

strFile = [Forms]![frm_Regional ELC]![txtItemDescription]
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_delete FCL"
DoCmd.OpenQuery "qry_temp FCL"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, "tbl_temp FCL", strFile, True
Call FormatDataInExcel
End Sub

all other code follows ...

Any idea why the transfer is having trouble?

Nov 13 '05 #3

P: n/a
Johnny ... thank you for the info. The excel file does not open as its
called in the code. I also get the following error.

"Compile Error: Method or data member not found"

This is the code I have in place ....
Public Sub SendDataToExcelAndFormat()
Dim strFile As String
strFile = [Forms]![frm_Regional ELC]![txtItemDescription]
FileName = [Forms]![frm_Regional ELC]![txtItemDescription]
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_delete FCL"
DoCmd.OpenQuery "qry_temp FCL"
DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acExport, , _
"tbl_temp FCL", _
FileName, True
Call FormatDataInExcel
End Sub
Private Sub FormatDataInExcel()
Dim strExcelWork*book As String
On Error GoTo Err_FormatDataInExcel
Dim exl As Object
'Attempt to assign an Excel application that already
'exists to object variable
Set exl = GetObject(, "Excel.Application")
'Open the workbook to format
exl.Workbooks.Open FileName:=strExcelWorkbook
'Your recorded macro code begins here

Error msg pops up at this point.-
Application.CutCopyMode = False
Selection.Cut Destination:=Range("A7")
Range("A2").Select
Selection.Cut Destination:=Range("B7")
Range("A8").Select
ActiveCell.FormulaR1C1 = "Season"
Range("B1").Select
Selection.Cut Destination:=Range("A9")
Range("B2").Select
Selection.Cut Destination:=Range("B9")
Range("C1").Select
Selection.Cut Destination:=Range("A10")

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.