Output to an excel file

[ChrisGow]

I am having a problem getting Access to output to an excel file.

I want to take a form that I have that has 20 or so records with 6 or 7 fields for each and put it into an excel file that is formated the way I need everythign to look.

I also need it to make the file name such as CCCAAAMMDDYYYY (3 letter company abbreviation,3 letter initials and then todays date) I am thinking that the company abbreviation will be pulled from a query, then initials will be a form that opens, and the date can be pulled from access' date function.

I tried using vb code and doing an output to comand but that did not work.
I am confused someone please help if possible.

 

[gromit]

Hi -

There is a lot involved in what you are describing. It certainly can be done, but it requires knowledge of a number of features in Access and Excel.

See below for how to at least create a Excel workbook via Access VBA. (Note: this particular example deletes any existing workbooks with the same name!).

Private Sub MakeWorkbook()

Dim objXL As Object, objXLSheet As Object
Dim objXLBook As Object

        
    ' If the file we want to build already exists
    If "" <> Dir(CurrentProject.Path & "\Sample.xls") Then
        ' .. then Delete it
        Kill CurrentProject.Path & "\Sample.xls"
    End If
    
    ' Start Excel - using late binding to avoid library problems
    Set objXL = CreateObject("Excel.Application")
    
    ' Create a new workbook
    Set objXLBook = objXL.Workbooks.Add
    
    ' Save it
    objXLBook.SaveAs CurrentProject.Path & "\Sample.xls"
    
    ' Point to worksheet
    Set objXLSheet = objXLBook.ActiveSheet
    
    ' Name the worksheet
    objXLSheet.Name = "Test_Data"
    
    ' Insert column headings
    objXLSheet.Cells(1, 1) = "Year"
    objXLSheet.Cells(1, 2) = "2000"
    objXLSheet.Cells(1, 3) = "2001"
    objXLSheet.Cells(1, 4) = "2002"
    objXLSheet.Cells(1, 5) = "2003"
    objXLSheet.Cells(1, 6) = "2003"
    
    ' Save what we've changed
    objXLBook.Save
    
    ' Clear the worksheet and workbook objects
    Set objXLSheet = Nothing
    Set objXLBook = Nothing
    
    ' Make Excel visible on the screen
    objXL.Visible = True
    
    ' Clear the object
    Set objXL = Nothing
    
End Sub

Take a look at the VBA reference for Excel as well.

- g

 

[ChrisGow]

Thanks, I think this will get me a good start into exporting. I can modify this code to do some of the automatic things that I need it to do.

Is it possible for me to set the spreadsheet up like this and then use the output to function or is there another way?

 

[gromit]

Not sure that I understand your question.... can you clarify what you mean by using the "output to function"?