Exporting to excel

[styphon]

Hi,

I'm new to access vba and need to export the results of a query to an excel document. The excel document has certain cells which I have to fill in to place an order. I want to automate this process but don't know where to start. Can someone please help me?

 

[boblarson]

Hi,

I'm new to access vba and need to export the results of a query to an excel document. The excel document has certain cells which I have to fill in to place an order. I want to automate this process but don't know where to start. Can someone please help me?

Check out this Excel Export code on my website.

http://www.btabdevelopment.com/main/CodeSnippets/tabid/104/Default.aspx

 

[styphon]

Hi, thanks for that, and sorry for long delay in reply (work's been mad n this got pushed to the bottom of the pile). Your code seems very generic though. I was hoping for more along the lines of a tutorial. Or some personal help. I need to export to specific cells. For example, I need the following:

Workbook "Provides"

mt_cust to export to column E
mt_ord_uin to export to column F
mt_pay_uin_nrc to export to column G
mt_pay_uin_r to export to column H
mt_title to export to column K
mt_initial to export to column L
mt_surname to export to column M
mt_email_addr to export to column O

I also have 2 other workbooks to be completed with similar queries but different results.

Any help would be greatly appreciated.

 

[chergh]

Try something like this:

dim xlapp as excel.application
dim wb as excel.workbook
dim ws as excel.worksheet

dim i as long
dim strSQL as string

dim rs as recordset


set xlapp = new excel.application
set wb = xlapp.workbooks.add
set ws = wb.worksheets("Sheet1")

strSQL = "select something from somewhere"

set rs = currentdb.openrecordset(strSQL)

i = 1
rs.movefirst

do while rs.eof = false

 ws.range("E" & i).value = rs!mt_cust
ws.range("F" & i).value = rs!mt_ord_uin

i = i + 1

rs.movenext

loop

Thats the general idea of it and you should be able to figure out the rest yourself.

 

[styphon]

Try something like this:

dim xlapp as excel.application
dim wb as excel.workbook
dim ws as excel.worksheet
 
dim i as long
dim strSQL as string
 
dim rs as recordset
 
 
set xlapp = new excel.application
set wb = xlapp.workbooks.add
set ws = wb.worksheets("Sheet1")
 
strSQL = "select something from somewhere"
 
set rs = currentdb.openrecordset(strSQL)
 
i = 1
rs.movefirst
 
do while rs.eof = false
 
 ws.range("E" & i).value = rs!mt_cust
ws.range("F" & i).value = rs!mt_ord_uin
 
i = i + 1
 
rs.movenext
 
loop

Thats the general idea of it and you should be able to figure out the rest yourself.

Excelent, I shall give it a shot and report back later. One other question though, what does the "rs.eof = false" bit specify?

 

[chergh]

eof is "end of file". It's a boolean value so once the record pointer lies after the last record eof becomes true.

In this context it serves to exit the loop once you have processed the last record.

 

[styphon]

eof is "end of file". It's a boolean value so once the record point lies after the last record eof becomes true.

In this context it serves to exit the loop once you have processed the last record.

Ah, thanks for that. Good to know.

 

[styphon]

OK, been a while, I know. But I finally got round to trying this out. Every time I run it I get a Compile error: User defined type not defined and it highlights the first row except the Dim expression -

xlapp As excel.Application

.

Any help with this would be appreciated as I am way out of my depth with this coding.

 

[boblarson]

If you do have a reference set (Tools > References) to Microsoft Excel then you need to use

[color=red]Dim[/color] xlapp As Excel.Application
[color=red]Set xlapp = New Excel.Application[/color]

 

[styphon]

If you do have a reference set (Tools > References) to Microsoft Excel then you need to use

[COLOR=red]Dim[/COLOR] xlapp As Excel.Application
[COLOR=red]Set xlapp = New Excel.Application[/COLOR]

Those lines were in the code, I just quoted the part that was highlighted by the debugger. But that was the problem, I didn't have the reference set. I now do and that error has gone.

 

[boblarson]

Glad you got it fixed.

 

[styphon]

I know this has been quite a while but my boss pulled me from this project for a while. I've just returned to it and I still can't get this to export. I have a Form on which there are two options to search by (Project Name or Order Number). The form also has 3 options for the order type (Cease, Change and Provide) as each one needs to be exported to a seperate worksheet. The results for each of the 3 order types are different as well.

The 2 queries I have both have 2 variables in them. The order type and then either then Project Name or the Order Number. The variables are loaded from the form.

There is then a button called Export. These loads another form (one for Project Name and one from Order Type) which execute the following code:

Private Sub Form_Load()
    Dim xlapp As Excel.Application
    Dim wb As Excel.workbook
    Dim ws As Excel.worksheet
 
    Dim i As Long
    Dim strSQL As String
 
    Dim rs As Recordset
 
 
    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Add
    If [Forms]![frm_ord_export]![order_type_frame] = 1 Then
        Set ws = wb.Worksheets("Ceases")
    ElseIf [Forms]![frm_ord_export]![order_type_frame] = 2 Then
        Set ws = wb.Worksheets("Changes")
    ElseIf [Forms]![frm_ord_export]![order_type_frame] = 3 Then
        Set ws = wb.Worksheets("Provides")
    End If
 
    strSQL = "[B]**LOCATION OF FILE**[/B]"
 
    Set rs = CurrentDb.OpenRecordset(strSQL)
 
    i = 2
    rs.MoveFirst
 
    Do While rs.EOF = False
 
        ws.range("E" & i).Value = rs!mt_cust
        ws.range("F" & i).Value = rs!mt_ord_uin
        ws.range("G" & i).Value = rs!mt_pay_uin_nrc
        ws.range("H" & i).Value = rs!mt_pay_uin_r
        ws.range("K" & i).Value = rs!mt_title
        ws.range("L" & i).Value = rs!mt_initial
        ws.range("M" & i).Value = rs!mt_surname
        ws.range("O" & i).Value = rs!mt_email_addr
 
        i = i + 1
 
        rs.MoveNext
 
    Loop
End Sub

Every time I click on the Export button I get the following error:

The action or method requires a Form Name argument.

No debbuger starts or anything, and with my limitted amount of knowledge I am at a loss as to how to proceede. Please can someone help?

 

[styphon]

*Bump* Can anyone help?

 

[chergh]

Why do you have:

strSQL = "**LOCATION OF FILE**"

This is supposed to be a SQL statement not a file location.

 

[chergh]

Are you sure the value being returned from your order_type_frame control is what you think it is?

From the code:

    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Add

This is creating a new workbook for you so unless you have a template set up your worksheets won't be called "ceases", "changes" and "provides".

 

[styphon]

Why do you have:

strSQL = "**LOCATION OF FILE**"

This is supposed to be a SQL statement not a file location.

OK, what kind of SQL statement? I have no experience with SQL. The original code above specifies

strSQL = "select something from somewhere"

so I assumed that meant the location of the file. What is this used for?

Are you sure the value being returned from your order_type_frame control is what you think it is?

From the code:

    Set xlapp = New Excel.Application
    Set wb = xlapp.workbooks.Add

This is creating a new workbook for you so unless you have a template set up your worksheets won't be called "ceases", "changes" and "provides".

Ah, OK. So how would I get it to open an existing document?

 

[chergh]

If you don't know SQL then just put the name of your query in the expression

 Set rs = CurrentDb.OpenRecordset("QueryNameHere")

To open an existing excel file use:

dim strPath as string

strPath = "C:\folder1\folder2\NameOfFile.xls"

Set xlapp = New Excel.Application
Set wb = xlapp.workbooks.open(strPath)

 

[styphon]

Neither of those solved the problem. I'm still getting the same error.

I removed the Error checking part, which allowed the debugger to start. It highlights the open form command. It looks to me as if it isn't even opening the form which exports the data. I get a run-time error 2494.

I set the form up and then manually opened the form that should export the code. I get another error, "run-time error 3601: Too few parameters. Expected 2", and the following line of code is highlighted when I start the debugger:

Set rs = CurrentDb.OpenRecordset("qry_ord_export_proj")

 

[chergh]

I didnt look to see which event you had attached this to. You don't want it on the form load event as there will be no data.

Create a button on the form you are opening and append the code to that button.

 

[styphon]

I didnt look to see which event you had attached this to. You don't want it on the form load event as there will be no data.

Create a button on the form you are opening and append the code to that button.

I've done this but both errors are still occuring.