Transfer data problem

[mrgunner]

I have this code to transfer data from a table to a existing spreadsheet and it works fine. My question is: Is there a way to do the code more flexible so I could choose if I want to change row to transfer to? For example, I would like it to transfer first to B6 to T6 and then change to B26 to T26.

Here´s the code I got.

Dim lngColumn As Long
        Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

        blnEXCEL = False

        ' Replace True with False if you do not want the first row of
        ' the worksheet to be a header row (the names of the fields
        ' from the recordset)
        blnHeaderRow = False

        ' Establish an EXCEL application object
        On Error Resume Next
        Set xlx = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
              Set xlx = CreateObject("Excel.Application")
              blnEXCEL = True
        End If
        Err.Clear
        On Error GoTo 0

        ' Change True to False if you do not want the workbook to be
        ' visible when the code is running
        xlx.Visible = True

        ' Replace C:\Filename.xls with the actual path and filename
        ' of the EXCEL file into which you will write the data
        Set xlw = xlx.Workbooks.Open("C:\Documents and Settings\g00010\My Documents\Ny mapp (2)\SCHEMA_KVART.xls")

        ' Replace WorksheetName with the actual name of the worksheet
        ' in the EXCEL file
        ' (note that the worksheet must already be in the EXCEL file)
        Set xls = xlw.Worksheets("Blad1")

        ' Replace A1 with the cell reference into which the first data value
        ' is to be written
        Set xlc = xls.Range("B6") ' this is the first cell into which data go

        Set dbs = CurrentDb()

        ' Replace QueryOrTableName with the real name of the table or query
        ' whose data are to be written into the worksheet
        Set rst = dbs.OpenRecordset("Niklas", dbOpenDynaset, dbReadOnly)

        If rst.EOF = False And rst.BOF = False Then

              rst.MoveFirst

              If blnHeaderRow = True Then
                    For lngColumn = 0 To rst.Fields.Count - 1
                          xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
                    Next lngColumn
                    Set xlc = xlc.Offset(1, 0)
              End If

              ' write data to worksheet
              Do While rst.EOF = False
                    For lngColumn = 0 To rst.Fields.Count - 1
                          xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
                    Next lngColumn
                    rst.MoveNext
                    Set xlc = xlc.Offset(1, 0)
              Loop
            
        End If

        rst.Close
        Set rst = Nothing

        dbs.Close
        Set dbs = Nothing

        ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
        Set xlc = Nothing
        Set xls = Nothing
        xlw.Close True   ' close the EXCEL file and save the new data
        Set xlw = Nothing
        If blnEXCEL = True Then xlx.Quit
        Set xlx = Nothing

 

[highandwild]

Your procedure is specifically written to cope with one scenario.

What I have done in the past is create a procedure that accepts the following as arguments:

Path
Workbook name
Worksheet name
Query name

A field in the query states the cell reference into which the data is to be written.

This way the code can be used to write data to where ever you like.

Your code could easily be changed to work in this way.

 

[mrgunner]

Thanks highandwild, I did it with a couple of querys and got it to work. I´m sure I didn´t do it the way you thought of but you made me think how I could get it to work anyway