Exporting report to excel with formatting

[ds_8805]

Hi Everyone! I think I have asked this question before but I still do not have a solution for it. I have written a query to generate the report I want. I have also managed to export the query to excel(This export is done thru vba coding). However, I would like the the exported excel spreadsheet to have the same format as the report. Is it possible to do this by vba coding?

Thanks for any help

 

[Galaxiom]

Anything is possible through VBA.
Open the Excel document after export and command its formatting with VBA.

However it would be easier to link a preformmatted Excel document into Access and update it rather than using the query export.

 

[ds_8805]

Hey thanks for replying me.
I would like the follow the suggestion that you gave which is to link a preformmatted Excel document into Access and update it rather than using the query export. Would you be able to explain how I would be able to do it? Sorry I do not know how to do this.

Thanks

 

[Galaxiom]

Format your Excel document as you require and keep a clean copy.
Put the document in a handy place and do a data import in Access using Link as the option.

The speadsheet will then appear as a table in Access. Turn your exported query into an update query to update that table.

 

[ds_8805]

Hi, Thanks for ur reply once again. I have tried as what you suggested. However, the link table does not get updated even though I run the update query. Moreover, I am not able to open my preformatted excel file also. Do you have any idea of what I might be doing wrong?

Is there an more efficient way to do this using vba coding?

Thanks for ur help

 

[Galaxiom]

I wrongly expected to be able to update an Excel linked table but it is a one way connection from Excel into Access. Access can do more with a text file.

Would it be suitable to do it from in Excel? Import the Access query from in Excel. You can delete the contents of the cells for the next import and you will be asked if you want to delete the query. Click No to keep the query. To get the latest data just click Data > Refresh all in Excel.

I tried this and it maintains the Excel formatting. (Office 2007)

 

[ds_8805]

Hey I tried the method u said. It works. However, I still need some more clarification. The query is used to generate monthly reports. Therefore I export the query every month into excel. The excel sheet is saved by the month's name. Thus, if I were to follow ur method, then this is not possible. Is there an solution for this?

Thanks

 

[DCrake]

Here is a cobbled together function to open a preformatted Excel workbook and populate it with data from Access

Public Function ExportToPreformattedExcelworkbook()
    'Declare Objects
    Dim oExcel As Object
    Dim obook As Object
    Dim oSheet As Object
    'Test for previous copy of file
    If Dir("C:\Temp\Recon.xls") <> "" Then
        If Dir("C:\Temp\ReconTemp.xls") <> "" Then
            Kill "C:\Temp\ReconTemp.xls"
        End If
        'Create a new blank formatted workbook
        FileCopy "C:\Temp\Recon.xls", "C:\Temp\ReconTemp.xls"
        Kill "C:\Temp\Recon.xls"
   End If
   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   
   Set obook = oExcel.Workbooks.Open("C:\Temp\ReconTemp.xls")
   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = obook.Worksheets(1)
   'There are various methods of populating the workbook
   'Enter the method you prefer here
   'The most common one would be to use the CopyFromRecordset command
   'Eg oSheet.Range("A2").Value = CopyFromRecordset NameOfRecordsetHere
   
   'Save the Workbook and Quit Excel
   'You could give the workbook a generic name or hard code it yourself
   obook.SaveAs "C:\Temp\Recon.xls"
   oExcel.Quit
   'Destroy object references from cache
   Set oExcel = Nothing
   Set obook = Nothing
   Set oSheet = Nothing
End Function

David

 

[ds_8805]

Hello! Thank you for replying me. I tried using the code that u sent me. However, I realised that I need a recordset in order to populate the workbook, but I am using just a query! Does that mean I have to create a recordset first? Can I just use query to populate the workbook? Sorry I m rather new at this. So I would be really grateful if you could help me out with this.

Thank You

 

[Galaxiom]

Can I just use query to populate the workbook? Sorry I m rather new at this.

No. That is what I mistakenly suggested first up.

Don't fear Recordsets. Creating them is not difficult and it is something you do need to learn about if you are going to get anywhere beyond starting out in Access.

http://msdn.microsoft.com/en-us/library/bb243786.aspx

 

[ds_8805]

Hey really thanks for ur help! the link u provided helped me understand loads!! Thanks!!
However I m still getting error in my code.

Public Function ExportToPreformattedExcelworkbook()
Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstProducts = dbsNorthwind.OpenRecordset("RMC(2G ONLY)_update")
'Declare Objects
Dim oExcel As Object
Dim obook As Object
Dim oSheet As Object
'Test for previous copy of file
If Dir("C:\100210\Recon.xls") <> "" Then
If Dir("C:\100210\ReconTemp.xls") <> "" Then
Kill "C:\100210\ReconTemp.xls"
End If
'Create a new blank formatted workbook
FileCopy "C:\100210\Recon.xls", "C:\100210\ReconTemp.xls"
Kill "C:\100210\Recon.xls"
End If
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")

Set obook = oExcel.Workbooks.Open("C:\Temp\ReconTemp.xls")
'Add data to cells of the first worksheet in the new workbook
Set oSheet = obook.Worksheets(1)
'There are various methods of populating the workbook
'Enter the method you prefer here
'The most common one would be to use the CopyFromRecordset command
oSheet.Range("A2").Value = CopyFromRecordset rstProducts

'Save the Workbook and Quit Excel
'You could give the workbook a generic name or hard code it yourself
obook.SaveAs "C:\Temp\Recon.xls"
oExcel.Quit
'Destroy object references from cache
Set oExcel = Nothing
Set obook = Nothing
Set oSheet = Nothing
End Function


The error that is thrown is at this line
oSheet.Range("A2").Value = CopyFromRecordset rstProducts

Am I using this correctly? I tried to search online but could not really understand whether this correct. I would greatly appreciate if you point out my mistake.

Thank you very much!

 

[DCrake]

Error in code


oSheet.Range("A2").Value = CopyFromRecordset rstProducts

Should read


xlSheet.Range("A2").CopyFromRecordset rstProducts

As I said code was untested

David

 

[ds_8805]

Hey! Thanks for all your help. I really appreciate it. However, I am still facing a problem. Now only part of the code works. It works until Recon is deleted. However, it says that object is required. I have just pasted my edited code below.

Private Sub Command22_Click()
On Error GoTo Command22_Click_Err

Dim dbsNorthwind As DAO.Database
Dim rstProducts As DAO.Recordset
Set dbsNorthwind = CurrentDb
Set rstProducts = dbsNorthwind.OpenRecordset("RMC(2G ONLY)_update")
'Declare Objects
Dim oExcel As Object
Dim obook As Object
Dim oSheet As Object
'Test for previous copy of file
If Dir("C:\100210\Recon.xls") <> "" Then
If Dir("C:\100210\ReconTemp.xls") <> "" Then
Kill "C:\100210\ReconTemp.xls"
End If
'Create a new blank formatted workbook
FileCopy "C:\100210\Recon.xls", "C:\100210\ReconTemp.xls"
Kill "C:\100210\Recon.xls"
End If
'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set obook = oExcel.Workbooks.Open("C:\100210\ReconTemp.xls")
'Add data to cells of the first worksheet in the new workbook
Set oSheet = obook.Worksheets(1)
'There are various methods of populating the workbook
'Enter the method you prefer here
'The most common one would be to use the CopyFromRecordset command
'oSheet.Range("A2").Value = CopyFromRecordset rstProducts
oSheet.Range("A2").Value.CopyFromRecordset rstProducts
'Save the Workbook and Quit Excel
'You could give the workbook a generic name or hard code it yourself
obook.SaveAs "C:\100210\Recon.xls"
oExcel.Quit
'Destroy object references from cache
Set oExcel = Nothing
Set obook = Nothing
Set oSheet = Nothing

Command22_Click_Exit:
Exit Sub
Command22_Click_Err:
MsgBox Error$
Resume Command22_Click_Exit
End Sub

Thank you for ur help again. Hope u could help spot my mistake.

Thanks

 

[wiklendt]

please use the code wrapper for code.

'Test for previous copy of file
If Dir("C:\100210\Recon.xls") <> "" Then
     If Dir("C:\100210\ReconTemp.xls") <> "" Then
         [B]Kill [/B]"C:\100210\ReconTemp.xls"
     End If
     
     'Create a new blank formatted workbook
     FileCopy "C:\100210\Recon.xls", "C:\100210\ReconTemp.xls"
     [B]Kill [/B]"C:\100210\Recon.xls"
End If

'Start a new workbook in Excel
Set oExcel = CreateObject("Excel.Application")
Set obook = oExcel.Workbooks.Open("C:\100210\ReconTemp.xls")

you're deleting a lot of files there and then trying to open some. to me this code looks like you can only really run this successfully once? because it deletes the file before you can use it again with the code?

 

[wiklendt]

sorry, my bad. upon closer inspection this actually looks ok, but i'm wondering why you are implementing this round-about method?

 

[wiklendt]

does the error point you to a particular line? have you tried setting a breakpoint to see where the code really gets up to?

 

[ds_8805]

Hey! Thanks for ur reply. Ya I agree with u on that part. I can just simply just use one file. I plan to change that part later when the rest of the code is working properly. The main problem now is that the data does not transfer to the excel spreadsheet and shows me an error 'Object required'. Would u have any idea on how I might be able to solve that problem?

Thanks

 

[Galaxiom]

Try replacing this:
Set oSheet = obook.Worksheets(1)

with:
Set oSheet = obook.Worksheets("your sheet name")

A number in the WorkSheets argument is the sheet index number and might not exist.