Reports output in Excel

[Gasman]

Will do. Thank you

 

[fat controller]

Thanks to you both

Just tried it, and get a different error -- stepping through the code, it is this bit that it doesn't like:

[/B]
Set qdf = CurrentDb.QueryDefs(strTQName)
            qdf![Forms]!PayrollPeriodExport![txtPerDate] = [Forms]![PayrollPeriodExport]![txtPerDate]
            Set rst = qdf.OpenRecordset()

Have I dropped a clog somewhere?

 

[Gasman]

Syntax on middle line does not match mine on the left of the = sign.

Check the [ & ]

 

[fat controller]

And we move on a little further.....

It now gets to

Set x1WSh.Worksheets.strSheetName

Before lobbing me out with a runtime error 438, 'Object doesn't support this property or method'

 

[Gasman]

Should be

Set xlWSh = xlWBk.Worksheets(strSheetName)

 

[Gasman]

You are changing code. That was not what you posted last night. That line was correct then. ?

Edit: I'm going to be offline for 30 mins or so. I need to fix my sisters computer over the phone, so might be a while.

 

[fat controller]

You are changing code. That was not what you posted last night. That line was correct then. ?

I don't recall changing it - I copied and pasted the code I had last night, and would swear I hadn't been anywhere near that line. Dementia beckons I think...

I have changed it as advised, and it works - and then falls over at the save line

 xlWBk.SaveAs "C:\Users\NicolR\Desktop\Russ Temp\FourWeeksEnding" & Date & ".xlsx", 51

The attached errors appear

 

[Gasman]

Does that path even exist?
Last night you had

             xlWBk.SaveAs "C:\Users\Russell\Documents\Payroll Test\Template\FourWeeksEnding" & Date & ".xlsx", 51

Edit: I don't think it will like the \ in the path, window will expect those to be folders.

Use the format function I posted previously. That will give you the date minus the "\"

Sister still hasn't called.

 

[fat controller]

Yes - I am on a completely different PC (at work tonight)

 

[Gasman]

Windows is going to think anything between the \ is a folder name, and anyway that value is not even the date.? It didn't work for me in 2003, though I got some strange string like AD01035, so I used the format function to get a valid reasonable date.

Try that format function.

Use the full name of file and add

& "_" & Format(Now(), "yyyymmdd") & ".xlsx", 51

at the end of the line in the SaveAS line

 

[fat controller]

OK - changed it to the suggested format, but I don't think its getting that far - - first thing to pop up is an error 1004 'Select method of Range class failed'

 

[Gasman]

Step through the code like we did last night and find which line gives the error.

Best post all the module code again to be on the safe side.

 

[fat controller]

Well I'll go to the top of our stairs....

I just stepped through the code, and as I did so - pop! up came an excel window, and it has copied everything across spot on!

All the links in the other sheets are blown to hell, but I can cope with that at a later point.

Now, if I call it for the second query, will it work.....??

Thank you - - I wish I could buy you and Gina a drink or five

 

[fat controller]

OK - first one works, second one fails - - - I suspect because the file is already open?

 

[fat controller]

I have just stepped through it once more - it runs through for the first query, spot on. It then goes back to the command button code and calls the next query, moves back to the module and highlights the module name then immediately goes to error handling

 

[Gasman]

Yes I get the error if I leave the file open.

Take out the quote for

        'ApXL.Quit

 

[fat controller]

OK - I have swapped it around so it is only running the second one, and it falls over at the line

x1Wsh.Range("A1").Select

 

[Gasman]

But what is the error?

 

[fat controller]

1004 Select method of Range class failed

 

[fat controller]

I have double checked - it is related to one query only; if I swap the query called to the other one, it falls over with that error.