Export to Excel Template

[phil123]

Hi,



I'm wanting to export data from Access in to an Excel template. I'm using a query and command button to do this, and so far have been able to export to Excel ok, however I want to send this to a template where the top 2 rows have nicely formatted heading. So essentially, I want to export data to row 3 of the excel template.

The methods I've used so far seem to overwrite the template rather than leaving the template as is and saving the output a new file. Therefore, if this is also possible, I want the export to use the template file, but ensure that a new file is saved leaving the template blank to be used again and again.

I seem to be getting lost in a world of VBA code, so any held will be greatly appreciated.

Thanks,

Phil

[ranman256]

Cant, but you can open excel, then COPYFROMRECORDSET to row 3.

'be sure to put excel object library, in vbe, tools, references

dim XL As Excel.Application
Set XL = CreateObject("excel.application")

set rst = currentdb.openrecordset("qsMyQuery")
with xl
  .Workbooks.Open sXLfile
  .range("A3").select
  .ActiveCell.CopyFromRecordset rst
end with
Set XL = nothing

[phil123]

Hi ranman256,

Thanks very much for your help. I've copied this code in to my database and changed the query, but unfortunately it doesn't appear to be working. Any suggestions?

Thank you.

[June7]

And what does 'not working' mean - error message, wrong results, nothing happens?

Post your exact code.

[phil123]

Hi June7,

I have copied the code exactly as above and have just changed the query to the corresponding one in my database. I have attached a picture of the error I'm getting when I click the command button.

Many thanks,

Phil

[June7]

Code is using early binding to Excel objects. Make sure you have Microsoft Excel x.0 Object Library activated in VBA editor: Tools>References

[ssanfu]

Also the top two lines on EVERY code module should be

Option Compare Database
Option Explicit   '<<<--- add this line

[phil123]

Hi all,

Thanks for your help so far, and apologies for the delay in getting back to you.

I have tried the codes as suggested and amended the Tools > References to the Excel object library but I am still getting an error message. If I leave out the 'Option Compare...' lines as suggested above the system looks to attempt opening Excel but then I get a different error message. If I leave these lines in, I get the same error as shown above.

I have uploaded a picture of the new error message and the code I have been using.

Thanks again everyone...really do appreciate all your help, I'm clearly an Access novice.

Phil

[June7]

What is the value of sXLFile? Where is this variable declared and set?

You are copying ranman's code but need to adapt it to your situation. Replace sXLFile with a string that is the path\filename of your workbook.

If you had Option Explicit in the module header as suggested by ssanfu, the compiler would probably have flagged this variable as undeclared because I doubt it has been declared anywhere.

[phil123]

Thanks for this.

I've changed this to the filepath, so my code now reads:

Private Sub Command0_Click()
Dim XL As Excel.Application
Set XL = CreateObject("excel.application")
Set rst = CurrentDb.OpenRecordset("qryExportAllProfBodies")
With XL
.Workbooks.Open.C:\Desktop\qryExportAllProfBodiesT emplate.xlsx
.range("A3").select
.ActiveCell.CopyFromRecordset rst
End With
Set XL = Nothing
End Sub


However, I keep getting an error message which says:

Compile error:

Expected: line number or label or statement or end of statement


Within the code, the line which contains the file path is now highlighted red.

[Minty]

It need to be in quotes as a string

.Workbooks.Open "C:\Desktop\qryExportAllProfBodiesT emplate.xlsx"

[June7]

Literal strings must always be within quotes or apostrophes.

[phil123]

Hi,

Thanks for this tip, I now seem to be getting somewhere. The only issue I now have is that Excel isn't actually opening. For testing purposes I'm just saving the template to my desktop. When I click the command button I can see the database processing and then an icon appears on my desktop (the type you see when you have a document open which is slightly faded). But Excel doesn't open. It's only when I double click the original file it opens. But in doing so I'm getting messages telling me I'm overwriting, etc. Any suggestions?

When I do open Excel the data is there.

Thanks
Phil

[Minty]

Try making the object visible;

With XL
    .Visible
    .Workbooks.Open"C:\Desktop\qryExportAllProfBodiesTemplate.xlsx"
    .range("A3").select
    .ActiveCell.CopyFromRecordset rst
End With

[phil123]

Afraid I just get an error message:

Compile error:

Invalid use of property