export to excel starting from Cell A2

[ice051505]

Hi dear all, I asked the export to excel question and get lots of help here, Thank you all so much!
Now my code is working prefectly with the ability that can export multiple queries to multiple worksheets. I just have one question here: Is there a way that I can make my data transfered starting from cell A2? I tried the way: arrStr![A2:Z1000], but it gave me the error said: Qualifier must be collection...I would appreciate any help on this subject, Thanks a lot!!!


Here is my original code:

Private Sub ExprDetail_Click()
Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim qdf2 As DAO.QueryDef
Dim rst As Recordset
Dim recordCnt As Integer
Dim recordNm As Integer
Dim sql As String
Dim sqlFull As String
Dim arrStr As String
Dim arrCount() As String
Dim path As String
Dim strPrmp As String
Dim strttl As String
Dim DflPath As String
DflPath = "C:\Documents and Settings\WangA\My Documents\Export_Excel_Files\FNexcel.xls"
strPrmp = "Would you like to name your Excel File?"
strttl = "Excel export"
path = InputBox(strPrmp, strttl, DflPath)
Const JOIN_SEP = ""","""

Set db = CurrentDb()
Set qdf1 = db.QueryDefs("RecordExcel")
Set rst = qdf1.OpenRecordset
recordCnt = rst.RecordCount
ReDim arrCount(1 To recordCnt) As String
rst.MoveFirst
For recordNm = 1 To recordCnt
    Set qdf2 = db.QueryDefs("DivideGroup")
    
    arrCount(recordNm) = rst!grouping
    arrStr = """" & arrCount(recordNm) & """"
        sql = "TRANSFORM Sum(Format_Step3.SumOfCatch) AS SumOfSumOfCatch SELECT Format_Step3.TimeGroup AS [Year] " _
             & "FROM Format_Step3 INNER JOIN RecordExcel ON Format_Step3.grouping = RecordExcel.grouping WHERE (((RecordExcel.grouping) In (" & arrStr & "))) " _
             & "GROUP BY Format_Step3.TimeGroup, RecordExcel.grouping ORDER BY Format_Step3.TimeGroup PIVOT Format_Step3.ClnVar;"
        qdf2.sql = sql
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DivideGroup", path, True, arrStr
   
    rst.MoveNext
    qdf2.Close
    
    Set qdf2 = Nothing
   
Next recordNm
    
End Sub

[June7]

What does arrStr hold?

Need to reference sheet name, like:

"Sheet1!A2:Z1000"

If arrStr is a variable holding sheet name then concatenate:

arrStr & "!A2:Z1000"

[ice051505]

Thanks so much, I have tried both way but none of them worked. I guess it because arrStr is an array string in my code...Is any way that I can apply the array variable in this situation?

If arrStr is a variable holding sheet name then concatenate:

arrStr & "!A2:Z1000"

[June7]

Wait, because you wanted to specify a range I 'read' export as import.

Transferspreadsheet cannot be used if you want to specify a start cell for export. Review http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[ice051505]

Ok, I see... So if I need to specify the range I will need to change the code? Do you know which way I can change it? Can you provide me an example? Thanks a lot!!!

Wait, because you wanted to specify a range I 'read' export as import.

Transferspreadsheet cannot be used if you want to specify a start cell for export. Review http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[June7]

Review http://forums.aspfree.com/microsoft-...el-414974.html

Look at the two additional links in the last post.

[ice051505]

Dear, the website you provide is can not be found :P

Review http://forums.aspfree.com/microsoft-...el-414974.html

Look at the two additional links in the last post.

[ice051505]

Sorry...the website is worked...my bad...

Review http://forums.aspfree.com/microsoft-...el-414974.html

Look at the two additional links in the last post.