how to export from access to excel /overwrite

[sspreyer]

hi

all

hope this is possible

right I have Query call "export to excel" these are columns in my query
employee id
total ex
date of ex
first name
surname
which I would like to export to excel file name "access data"

columns in excel
A
employee id
b
total ex
c
date of ex
d
first name
e
surname

now my problem is I cant manage to export the data to existing sheet within excel when I export it opens the existing file but create a new sheet / tab but I just want to delete the data in columns A,B,C,D only refresh the data in these columns when the user hits the command button in access on my form and takes the data from my query

not sure if this is possible
thanks in advance


cheers

shane

 

[CJ_London]

just to complete the picture - what about column E? Is this not to be deleted?

 

[sspreyer]

Hi cj london
e column not to be deleted I only want to delete a,b,c,d columns

Cheers

Shane

 

[CJ_London]

sounds risky to me - what if you have a new employee or lose one or the data is in a different order?

I'm signing off now, so my apologies in advance for not replying to your next post

 

[sspreyer]

cj London
The data it exports is not important and doesn't matter about the order just want it to update the worksheet even if it does not delete a b c d just overwrites new employee and important data is held in a separate table


Good night
Cheers

Shane

 

[CJ_London]

Have a look at these links

http://www.btabdevelopment.com/ts/default.aspx?PageId=49

http://stackoverflow.com/questions/14562209/export-from-access-to-excel-to-an-existing-worksheet


and using TransferSpreadsheet, I must admit I thought Range did not work for exporting data, but post #6 in the link below indicates it does for a late enough version of Excel

http://www.mrexcel.com/forum/micros...putting-query-specific-excel-spreadsheet.html

 

[namliam]

transferspreadsheet has its limitations and problems, if you want to be exact about it have a look at post #7 of this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=259994

 

[sspreyer]

right I have put the code from post #7 from namliam post in to module in access
http://www.access-programmers.co.uk/...d.php?t=259994

im calling it from access using

 SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Excel Exposure Grapth"

but im getting error

error information
error 3061 too many parameter. Expected 1.

not sure what im doing wrong now

shane

 

[GinaWhipp]

Does your query require parameters to run?

 

[sspreyer]

Does your query require parameters to run?

thanks Gina!

right I have found the problem the command button is on my sub form called frmexposure on a tab on my main form frmemployee

yes the only parameter it requires to run is employeeid from frmemployee
[Forms]![frmemployee]![employeeId] this is in the criteria but both forms are open when exporting if I remove the criteria it works but does not apply the employeeid filtering that I need

is there anything I can do to resolve this issue

thanks again

Shane

 

[GinaWhipp]

Okay, so you need something like this on the Command Button that runs the report...

        Dim strSQL As String
 
    strSQL = "*****[B]YourQuerySQLWithoutTheParameter[/B] goes here"
 
    'Numeric
    If Not IsNull(Me.employeeId) Then
        strWhere = strWhere & "([*****[B]NameOfFieldFrmQuerySQL[/B]] = " & Me.employeeId & ") AND "
    End If
 
lngLen = Len(strWhere) - 5
 
        DoCmd.Hourglass True
 
        If lngLen <= 0 Then
            strSQL = strSQL
            Set qryDef = dbs.CreateQueryDef("ExposureGraph", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "ExposureGraph"
            DoEvents
            DoCmd.DeleteObject acQuery, "ExposureGraph"
        Else
            strWhere = Left$(strWhere, lngLen)
 
            strSQL = strSQL & " WHERE " & strWhere
            Set qryDef = dbs.CreateQueryDef("ExposureGraph", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "ExposureGraph"
            DoEvents
            DoCmd.DeleteObject acQuery, "ExposureGraph"
        End If
 
        DoCmd.Hourglass False
 
    Else
        DoCmd.CancelEvent
    End If

Make sure you update the parts were you see the asterisks to match your data, the balance *should* be oaky.

What you do with the above is create a *temp* query with the parameter already set based on your selection on the Form. Then you use that query to export to Excel and finally, once the export is complete, you delete the *temp* query.

 

[sspreyer]

right I have put the code on my command button see below

   Dim strSQL As String
 
    strSQL = "SELECT tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname FROM tblExposure INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID GROUP BY tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure], tblemployee.firstname, tblemployee.lastname;"
 
    'Numeric
    If Not IsNull(Me.employeeId) Then
        strWhere = strWhere & "([employeeId] = " & Me.employeeId & ") AND "
    End If
 
lngLen = Len(strWhere) - 5
 
        DoCmd.Hourglass True
 
        If lngLen <= 0 Then
            strSQL = strSQL
            Set qryDef = dbs.CreateQueryDef("Exposure Graph", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Exposure Graph"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Graph"
        Else
            strWhere = Left$(strWhere, lngLen)
 
            strSQL = strSQL & " WHERE " & strWhere
            [B][COLOR=red]Set qryDef = dbs.CreateQueryDef("Exposure Graph", strSQL)[/COLOR][/B]
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Exposure Graph"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Graph"
        End If
 
        DoCmd.Hourglass False
 
    
        DoCmd.CancelEvent
    
End Sub

right now I m getting error
run time error 424 object required

red bold show my error line

I'm a officially lost

cheers

shane

 

[GinaWhipp]

That is because the query is not being created because it can't because there is something wrong with the SQL.

I would suggest you copy the SQL as you have it and try to create the query. Note, you cannot have a query qith that same name in your database or it still won't create. So, if you already have a query with that name change the name of the one you are creating on the fly.

 

[sspreyer]

right here my sql

SELECT tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblExposure.[Low ex] AS Expr1
FROM tblExposure INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID
GROUP BY tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure], tblExposure.[Low ex];

here my vba version of SQL

 "SELECT tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname FROM tblExposure INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID GROUP BY tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure], tblemployee.firstname, tblemployee.lastname;"

can you see anything wrong with the code /SQL
I have also rename check the name of my query none match what in the code

so there is something to create

but I still get the run time error 424


thanks for help

shane

 

[namliam]

Never write long lines of SQL in just a single line.... atleast seperate the key words....
like so

strSQL = " SELECT tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname " & _ 
         " FROM tblExposure " & _ 
         " INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID " & _
         " GROUP BY tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure], tblemployee.firstname, tblemployee.lastname;"

Or better yet, make it properly readable by doing something like:

strSQL = " SELECT tblemployee.employeeId " & _
                " , tblExposure.Dateexposure" & _
                " , [Valuef/cm]*[D

"proper" SQL should read something like:
Select
From (including joins)
Where
Group by
Having
Order by

since your code is adding the where behind the (otherwize seemingly useless) group by, the sql is invalid

 

[sspreyer]

cheers
namliam for the tip

im still getting run time error 424 can t see why,
error line is in red

Private Sub test_Click()
       Dim strSQL As String
 
    strSQL = "SELECT tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname " & _
         " FROM tblExposure " & _
         " INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID " & _
         " GROUP BY tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure], tblemployee.firstname, tblemployee.lastname;"
    If Not IsNull(Me.employeeId) Then
        strWhere = strWhere & "([employeeId] = " & Me.employeeId & ") AND "
    End If
 
lngLen = Len(strWhere) - 5
 
        DoCmd.Hourglass True
 
        If lngLen <= 0 Then
            strSQL = strSQL
            Set qryDef = dbs.CreateQueryDef("Exposure Grapth1", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Exposure Grapth1"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Grapth1"
        Else
            strWhere = Left$(strWhere, lngLen)
 
            strSQL = strSQL & " WHERE " & strWhere
          [COLOR=red][B] Set qryDef = dbs.CreateQueryDef("Exposure Graph1", strSQL)
[/B][/COLOR]            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\Exposure Grapth - Copy.xlsm", "Exposure Grapth1"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Graph1"
        End If
 
        DoCmd.Hourglass False
 
    
        DoCmd.CancelEvent
    
End Sub

 

[namliam]

....
"proper" SQL should read something like:
Select
From (including joins)
Where
Group by
Having
Order by

since your code is adding the where behind the (otherwize seemingly useless) group by, the sql is invalid

Your sql isnt proper as per above....
You have
Select
From
Group by
Where

instead of
Select
From
Where
Group by

 

[sspreyer]

sorry but im very very lost havent got clue with sql

so your saying the problem is WHERE Statement is after the group by

so would my where statement be WHERE = strwhere then I guess it would then look @ the WHERE Statatment First
to be honsest I m way above my head now don't no where to start
:banghead::banghead:

thanks

shane

 

[namliam]

First I would suggest you try creating "proper" sql, which in this case you do by removing the surplus Group by clause....

    strSQL = "SELECT tblemployee.employeeId, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname " & _
         " FROM tblExposure " & _
         " INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID " & _

Try that

 

[namliam]

In a bit more detailed...

In sql there are certain keywords that NEED to be in a certain order, which I posted above.
Select
from
Where
Group by
Having
Order by

If these words do not appear in this order anything bar the Select and from can be skipped i.e.
Select
from
Order by

however certain rules do apply you cannot have a having without group by.... for example.

"easy" solution to make good SQL is to use the query designer, make the EXACT query you are trying to make in VBA and copy the SQL from there.

Hope this has helped you a little more than my earlier somewhat non-descriptive posts.