Help needed on export to excel 1

[wambaugh]

Hi All

Does any one know How (or if it's even possible) to transfer data from a query to a specific cell in an excel spreadsheet?

I have a form based on a query which has controls in it and I want to be able to extract data from a control (say me.text0) and send it to a spreadsheet in a particular cell (say A1)

Is it possible?

I have tried using Transferspreadsheet but I can only get it to send the whole query

Any help appreciated

wambaugh

 

[TomHW]

You're in luck! It can be done, unfortunately it isn't extremely easy. You can use automation to connect to an instance of Excel and then can do quite a bit with it.

The following code opens an instance of an excel workbook. From there it creates an instance of an excel application and an excel worksheet. The application is used to actually make this all visible but is not necessary in this case. From the worksheet you can access any object on the worksheet such as a range.

Placing this function in a module and then calling:

ControlExcel "A1", me.text0, "C:\Test.xls"

from your form should do exactly what you asked. You should replace the path with the path to your file.

Public Function ControlExcel(strRange As String, strValue As String, strPath As String) As Boolean
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    
On Error GoTo HandleErrors:

    [COLOR=green]' Set this to the path of your file[/color]
    strPath = "C:\Test.xls"
    
    [COLOR=green]' Create a workbook object from an existing excel file[/color]
    Set xlBook = GetObject(strPath)
    
    [COLOR=green]' Create an application object from that workbook[/color]
    Set xlApp = xlBook.Parent
    
    [COLOR=green]' Create a worksheet object from the workbook
    '  You can substitute the actual worksheet name instead
    '  of using its number, eg. xlBook.Worksheet("TestSheet")[/color]
    Set xlSheet = xlBook.Worksheets(1)
    
    [COLOR=green]' Show the application so you can actually see whats going on
    '  Although not necessary here because we will quit when
    '  it is finished[/color]
    xlApp.Visible = True
    xlBook.Windows(1).Visible = True
    
    [COLOR=green]' Set our value in the ocrrect range[/color]
    xlSheet.Range(strRange) = strValue
    
    [COLOR=green]' Save and quit[/color]
    xlBook.Save
    xlApp.Quit
    
    UpdateExcel = True
    
ExitClean:
On Error Resume Next

    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    
    Exit Function
    
HandleErrors:
    MsgBox "ERROR " & Err.Number & ":" & vbCrLf & vbCrLf & Err.Description, _
            vbExclamation, _
            Err.Number
    Resume ExitClean
    
End Function

There is quite a bit more you can do with automation but this should solve your problem. You can access pretty much Excel's entire object library and this also works for most every other Office app. you can also create a new excel spreadsheet as opposed to opening an exisitng one.

Hope this helps,
Tom

 

[wambaugh]

Thanks TomHW

Just what I Needed

Thanks

Wambaugh