Guys I am having a blond moment

I have a qry that i wish to export to xls - but i want it to be automated

what i have is transactions and I need to have these exported in xls on the following basis

Sterling transaction - with
tax type 1 (5%)
tax type 2 (2%)
tax type 3(N/A)
etc
then .
euro
tax type 1 (5%)
tax type 2 (2%)
tax type 3(N/A)
etc
Dollars
tax type 1 (5%)
tax type 2 (2%)
tax type 3(N/A)
etc
other currencies - South african Rand - Idian Ruples etc
all seperated out
by currency and by tax
now my reports - done - fine
the xls transfer is throwing me

I know there is a key word that shows me how to do it (So let me know this word and then i should be able to take this a step forward)

now how I intend on doing this (given my limited knowledge on coding) is to run 1 qry per currency per tax rate and export into a xls book and each qry to be on a seperate tab/sheet

so let assume that each currency will have 3 tax rates (there willb e more)
so i will need 3 per currency in the example above
sterling , euor and dollar giving 9 qry to make - now the qry themselves are easy no problem on this - but is this the best way to do this or is there a better way ??

your thought smuch appricated
g

weekend everyone gone down the pub?:cool:

Hi Gary, the following code should get you started:

Dim dbs As DAO.Database

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "EnterSheetNameHere"

Set rstGetRecordSet = dbs.OpenRecordset("PlaceRecordSetQueryHere;")

objActiveWkb.Worksheets("EnterSheetNameHere").Cell s(1, 1).CopyFromRecordset rstGetRecordSet

objActiveWkb.Worksheets(1).SaveAs FileName:="EnterFileNameHere.xls"
objActiveWkb.Close

Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing

Blimey this looks complete -

I have to have a play with this at home, but many thanks Allan57.


g

Ok guys what have i done wrong here
trash1 name of worksheet required
the qry i want it to do is trash
and I want it to save the file as trashfile
( i would like to be able to change where i save it if poss?

any pointers would be appricated
g(might not get back on this for 48 hours):cool:



Dim dbs As DAO.Database

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Trash1"

Set rstGetRecordSet = dbs.OpenRecordset("Trash;")

objActiveWkb.Worksheets("Trash1").Cells(1, 1).CopyFromRecordset , rstGetRecordSet

objActiveWkb.Worksheets(1).SaveAs FileName:="TRashfile"
objActiveWkb.Close

Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing


End Sub

just bumping this up

Hi Gary

To save the spreadsheet in a location use the following,

objActiveWkb.Worksheets(1).SaveAs FileName:="c:\anyfolder\anyfile.xls"

i'll try that -
I cut and psted the code and debugged (?) and it didn't throw a hissy fit - which is good

I hope this works, co's then I can deficer it and try to understand how it all works

for your info

where i put trash - this was a name of a dummy qry , I built - and not to reflect anything other than for me to know that the qry/xls were to be trashed....etc (i don't want to put anyones nose out of joint.)
g

getting there :-

right it opens xls, and names sheet 1 to trash1(all as it should be
however throws a fit at :confused: so what the flip flop have I left out ????
any pointers most grateful

code below:0


Dim dbs As DAO.Database

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Trash1"

Set rstGetRecordSet = dbs.OpenRecordset("Trash;"):confused:




objActiveWkb.Worksheets("Trash1").Cells(1, 1).CopyFromRecordset , rstGetRecordSet
objActiveWkb.Worksheets(1).SaveAs FileName:="c:\trash\dump"
'
objActiveWkb.Close

Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close

bump...

g

getting there :-

Set rstGetRecordSet = dbs.OpenRecordset("Trash;"):confused:


Is the semi colon in "Trash;" supposed to be there?

Is the semi colon in "Trash;" supposed to be there?

Tried it with and without ??

Try:


Set rstGetRecordSet = dbs.OpenRecordset("Trash")

objActiveWkb.Worksheets("Trash1").Cells(1, 1).CopyFromRecordset rstGetRecordSet


Also what is the error message

Also try

set rstGetRecordSet = dbs.querydefs("Trash").openrecordset

Ahh that might be it, cos when I delet and retype it does that autofill in coding and it looks like it is asking for critia -
have to try this later - the programme i am using this on is at home and nbot at work.

g

Gary

I've never used a query name in the OpenRecordset statement, I always use an SQL statement. ie

Set rstGetExportData = dbs.OpenRecordset("SELECT tblEP2_Run_Hours_And_Electrical_Consumption.dtmDat e, tblEP2_Run_Hours_And_Electrical_Consumption.sngRun _Hours, tblEP2_Run_Hours_And_Electrical_Consumption.lngEle ctrial_Consumption " & _
"FROM tblEP2_Run_Hours_And_Electrical_Consumption " & _
"WHERE tblEP2_Run_Hours_And_Electrical_Consumption.txtDat e Like '" & strRecordsetCriteria & "*';")

Try using an SQL statement instead

now I am in well over my head ... I'll have to read up on this.. and see if I can understand this

-
Is it possible to do it from a qry?,which would be great ,
I could mess around with make table qry and delete tables straight away - to make this work - but would be easier on a simple soul like me to do it in straight qry.

Gary

I've never used a query name in the OpenRecordset statement, I always use an SQL statement. ie



You might want to think about it as it stops your code looking so messy or at the very least have:

strSQL = "select * from whatever"

Set rstGetExportData = dbs.OpenRecordset(strSQL)

what i think I will do is post the d/b up (later on today/this evening) and if anyone has a chance to play ---
this is still in a pretty rough format- and needs tiding up .

g

came accross this -

Private Sub Command1_Click()
'Produce "Tbl1XLS" excel spreadshit.
'***********************************
DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLS, "Tbl1XLS.xls", True

End Sub


So if this works (remember i am away from my test data)
all I need to do is figure out how to get it to put each qry into a seperate sheet

any pointers ??

Don't think the OutputTo method is going to be able to do what you want. you really need to use the sort of code you already have.

Gary, try using an sql statement when opening the recordset rather than a query name.

huhh..

could you expand - I can hack code - but this is slightly outside of my comfort zone

I am going to sta\rt another around this , to see if there are any other views - but please lets keep this one going - as it does
make a tab xls sheet
(i'll cross refer to this one )



DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLS, "Tbl1XLS.xls", True

just wait till you get home and try the new stuff you have here one step at a time.

I use query names regularly when opening recordsets so it's nothing to do with that.

Gary, below is an example of using a SQL statement when opening a recordset:

Set rstGetExportData = dbs.OpenRecordset("SELECT tblM14_Ctg_Period_vs_Engeries_Elec_Consumption_Cha rt_Report.txtDate, tblM14_Ctg_Period_vs_Engeries_Elec_Consumption_Cha rt_Report.intCoating_Value, tblEnergies_Consumption_vs_Ctg_Flag.dblEnergies_Co nsumption, tblEnergies_Consumption_vs_Ctg_Flag.intDate_Bucket _ID, tblEnergies_Consumption_vs_Ctg_Flag.txtTime_Bucket " & _
"FROM tblM14_Ctg_Period_vs_Engeries_Elec_Consumption_Cha rt_Report INNER JOIN tblEnergies_Consumption_vs_Ctg_Flag ON (tblM14_Ctg_Period_vs_Engeries_Elec_Consumption_Ch art_Report.txtTime_Abbreviated = tblEnergies_Consumption_vs_Ctg_Flag.txtTime_Bucket ) AND (tblM14_Ctg_Period_vs_Engeries_Elec_Consumption_Ch art_Report.bytDate_Bucket_ID = tblEnergies_Consumption_vs_Ctg_Flag.intDate_Bucket _ID) " & _
"WHERE tblEnergies_Consumption_vs_Ctg_Flag.dblEnergies_Co nsumption <> 0 " & _
"ORDER BY tblEnergies_Consumption_vs_Ctg_Flag.intDate_Bucket _ID, tblEnergies_Consumption_vs_Ctg_Flag.txtTime_Bucket ;")

So sql statement
is code for field names yeah

I 'll have to digest and read me book... ahhhh
well as long as I leanr something new

Don't bother writing the SQL statement if you don't already know how, stick to the query designer. Writing it out like that just makes your code harder to read.

Gary

I created a select query form one of my data tables and named it trash and using the following code created the spreadsheet and saved it. Gary see if it works for you.

Dim dbs As DAO.Database

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Trash1"

Set rstGetRecordSet = dbs.OpenRecordset("Trash1")

objActiveWkb.Worksheets("Trash1").Cells(1, 1).CopyFromRecordset rstGetRecordSet
objActiveWkb.Worksheets(1).SaveAs FileName:="c:\trash.xls"

objActiveWkb.Close

Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing

magic - will do
I can see a difference without having to test this straight away ..
but as I mentioned, need to get home to test this .

and thanks to both of you for chipping in - will advise how I am getting on with this
regards

g

Guys this didn't work
attached is a zip version

the form is trial, only button there. - I must be missing something very simple

:(

bump...........

g:rolleyes:

The problem seems to be your references.

Goto Tools > References in the VBA editor. Make sure you have Microsoft DAO object library and the Microsoft Excel Object Library both checked and make sure you do not have the Microsfot ActiveX Data Objects Library checked, if it is uncheck it.

Once you have done this it should work fine.

Yeah......
works - now to tweak it ...

many thanks


regards(on hols at the mo)


gary:D

guys first up

many thanks

now I need to add another qry to this
lets call it ABC and this i want to go onto a fresh sheet (in thesame W/book)
any pointers ??

Hi Gary, i've inserted the code below in dark red.

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Trash1"

Set rstGetRecordSet = dbs.OpenRecordset("Trash1")

objActiveWkb.Worksheets("Trash1").Cells(1, 1).CopyFromRecordset rstGetRecordSet

objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(2).Name = "ABC"

Set rstGetRecordSet = dbs.OpenRecordset("ABC_Query_Name")

objActiveWkb.Worksheets("ABC").Cells(1, 1).CopyFromRecordset rstGetRecordSet

objActiveWkb.Worksheets(1).SaveAs FileName:="c:\trash.xls"

objActiveWkb.Close

Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing

magic.....
and i can just keep going - ( will need at least 10 but could be up to 30 sheets).

big thanks to all - this one really throw me, and is (was ) one of the hang ups I had , still need to finish off this project, but this was a big hurdle and you have all helped.

G.
:D

Glad we could all help, below is some hints on formating/writing to cells and ranges that you may find useful.

'Cells(R, C):- R=Row, C=Column
'objActiveWkb.Worksheets("WorkSheetName").Cells(1, 1) = "Hi Gary"

'objActiveWkb.Worksheets("WorkSheetName").Cells(1, 1).Font.Name = "Aharoni"
'objActiveWkb.Worksheets("WorkSheetName").Cells(1, 1).Font.Size = 14

'objActiveWkb.Worksheets("WorkSheetName").Columns.AutoFit 'Adjust Column sizes to fit exported text width

'objActiveWkb.Worksheets("WorkSheetName").Cells(5, 1) = 4 'Place a value in cell A5
'objActiveWkb.Worksheets("WorkSheetName").Cells(6, 1) = 14 'Place a value in cell A6
'objActiveWkb.Worksheets("WorkSheetName").Cells(7, 1) = 78 'Place a value in cell A7

'objActiveWkb.Worksheets("WorkSheetName").Cells(8, 1).Formula = "=sum(a5:a7)" 'Sum the Value of cells A5>A7
'objActiveWkb.Worksheets("WorkSheetName").Cells(8, 2) = "Total"

'objActiveWkb.Worksheets("WorkSheetName").Range("a5:b7").NumberFormat = "0.00"
'objActiveWkb.Worksheets("WorkSheetName").Range("a8:b8").Font.Bold = True
'objActiveWkb.Worksheets("WorkSheetName").Range("a8:b8").Font.Color = 128
'objActiveWkb.Worksheets("WorkSheetName").Range("a8:b8").Interior.ColorIndex = 7
'objActiveWkb.Worksheets("WorkSheetName").Cells(6, 1).Value = "Some text here"
'objActiveWkb.Worksheets("WorkSheetName").Range("A1:B6").Font.Size = 8
'objActiveWkb.Worksheets("WorkSheetName").Range("B7:C13").Interior.Color = 10026745
'objActiveWkb.Worksheets("WorkSheetName").Range("B7:C13").Borders.Weight = 2
'objActiveWkb.Worksheets("WorkSheetName").Columns("A:C").EntireColumn.AutoFit
'objActiveWkb.Worksheets("WorkSheetName").DisplayGridlines = False
'objActiveWkb.Worksheets("WorkSheetName").WindowState = xlMaximized
'objActiveWkb.Worksheets("WorkSheetName").DisplayGridlines = False

Yeah, i kinda figure some of this already, but this will be helpful...g
:D
and thanks agasin