exporting to excel

[madrappin]

Is there any way to export a form into Excel, and automatically format it a certain way? Or export 3 or 4 reports into one excel file as separate worksheets? Thanks for any help.

 

[M8KWR]

Lets go through you questions in stages:-

When you say export a form, do you mean the form, or the data in the record that is been displayed on the form?

You can export more then one table/query into one excel file, and call the sheets different names, and the excel file inself.

The way to do this would be have your tables or queries called what you require the sheets within the file to be named.

Then what you would do is export each one of these tables or queries into the same excel file:

example below

Dataexport = "c:\Excel Test.xls"'this name the path of the excel file you need to write to

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Test1", Dataexport, True

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Test2", Dataexport, True

The above example assumes that you have a table or query names test1 and test2. It would export these two tables/queries into an excel file called "Excel Test" on your c drive.

Hope this helps, let me know about the first point so i can guide you in the correct direction

 

[madrappin]

I dont need to export only the one record. I have a form that I use to set which filter I open this report with. Then I need to export the report into excel. I would like to set it up someway that I can export each report that I filter into a separate worksheet, or if I can set it up all in one step that would be ideal. Thanks a lot for your help.

 

[M8KWR]

the report should have either a query or a query within the report. If you have a query within the report then you would have to create one for each report.

Then use this query to export to excel with the:-

DoCmd.TransferSpreadsheet acExport

If you give me the names of your queries, then i can write the code for you to use to achieve this. Also if you give me the path and the filename of the excel file you wish to create, then I can do all of it for you.

if you want the user to select where there put the file and what it is called then this can be achieved as well.

Hope this all makes sense to you.

 

[madrappin]

Thanks a lot! That actually works perfectly. I dont really even need the report. I just set up a few queries and export those. If you could tell me how to give the user the option of where to save the file that would be great. I would also like to know if its possible to set the font that the queries will use once exported to excel. Thanks for any help.

 

[M8KWR]

if you want the user to select where they want to put the excel file, and what they wish to call it follow these instructions below:

In a command button put this code (all you would need to is put in your query name)

    Dim FilePath1, FileNamePath1, Dataexport

    FilePath1 = LaunchCD(Me)

    
    If FilePath1 = "" Or IsNull(FilePath1) Then
    Else

    FileNamePath1 = FilePath1 & ".xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Contact Report Query", FileNamePath1, True

    End If

Then create a new module and insert all of this code in:-

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Function LaunchCD(strform As Form) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.Hwnd
    sFilter = "Excel Files (*.xls)" & Chr(0) & "*.xls"
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "C:\"
    OpenFile.lpstrTitle = "Select an Microsoft Excel file"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select an Microsoft Excel file"
         Else
            LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function

In reference to your font, i'm unsure, i think the only way this would be possible if you changed your default font within excel. I have not tested this myself, so unsure if this would work.

But everything else will works fine.

 

[madrappin]

Thanks a lot! That works perfectly.
...Just out of curiousity, I know how to make a multi-select list box of all the queries that I have. Is it possible to replace the part of code with the query name with the value from the list box? I think I know how to make that work if its possible only I'm not sure if it is because of selecting different numbers of queries. Just curious. Thanks again for your help.

 

[M8KWR]

yes, to get the string out the list box use

Something = List_Box_Name.Column(1)

It does depend on what column is bound so the 1 could change to 0, and so on..

Then replace the name where you would put the query directly into the code, with "Something".

Hope this helps.

Now you cna help me, how do you get the list of queries into a list box??? without typing them in yourself...

 

[madrappin]

In the rowsource just use...
<code>
SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND ([MSysObjects].[Type])=5 ORDER BY [MSysObjects].[Name];
</code>

There is FAQ that explains how to pull any of the objects into a listbox. faq181-690

 

[madrappin]

And thanks, yet again for the excellent help. That works perfectly!