Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28

    Uploading Sales Rep Data into Individual Excel Templates

    I have an Access crosstab query that contains sale rep information by customer for the last few years to aid in doing the annual business plan. I want to upload this into an Excel template file that I created that will give totals and allow for input for the remainder of the current year and next year by month.

    I have manged to create Excel files for each rep but cannot figure out how to upload it into the template starting at cell A23. Tried both Docmd.outputto and docmd.transferspreadsheet but cannot seem to get either to satisfy what I need.

    Any assistance would be appreciated.



    The current code:

    Private Sub Command20_Click()

    Dim db As DAO.Database
    Dim rs As Recordset
    Dim v As String
    Dim ZFilename As String

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("select distinct saname from salesinfoforcurryrplan_crosstab")

    Dim strqry As String
    Dim qdftemp As DAO.QueryDef
    Dim strQdf As String
    strQdf = "_TempQuery_"
    Do While Not rs.EOF
    'v = rs1.Fields(0)

    strqry = "select * from salesinfoforcurryrplan_crosstab where saname = '" & rs!saname & "'"
    Set qdftemp = CurrentDb.CreateQueryDef(strQdf, strqry)
    qdftemp.Close
    Set qdftemp = Nothing

    ZFilename = CurrentProject.Path & "\excelfilestorepsforinputdata" & "ZZ_Plan_Sales " & rs!saname & ".xlsx"


    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQdf, ZFilename, True
    CurrentDb.QueryDefs.Delete strQdf
    rs.MoveNext
    Loop
    rs.Close

    Dim fullpath As String

    fullpath = CurrentProject.Path & "\2salesdatalink.xlsx"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "salesinfoforcurryrplan_crosstab", fullpath, -1, "Sheet1"
    MsgBox "Sales data uploaded to Excel.", vbOKOnly

    End Sub
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Instead of all the QueryDef stuff, open a recordset on strqry. Then instead of OutputTo or TransferSpreadsheet use automation to open the template file and the CopyFromRecordset to dump the data starting at the desired cell. Searching for CopyFromRecordset should turn up the appropriate code.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you decide to use automation, as Paul suggests, Ken Snell has a site with Access/Excel automation examples http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    Look for Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)

  4. #4
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks Paul for your input. I'll rethink my approach to the solution. Locations of examples and snippets are useful for us non-coders.

  5. #5
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks Steve, haven't had a look at the site, but since I am a snippet stealer, it should be useful.

  6. #6
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    I have managed to download and adjust some code to get my data into an Excel template in the proper cells and save them under a different name. This file, however, contains the data for all the sales reps. What I need to do is loop through the source file, download the data by rep and save the file with the rep's name included. This is a bit out of my league, so some assistance would be muchly appreciated. I have listed the current code below and can give the structure of the source file.

    Dim lngColumn As Long
    Dim xlx As Object
    Dim xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strPathFileName As String, strWorksheetName As String
    Dim strRecordsetDataSource As String
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    Dim SANAME As String
    Dim tmpltfile As String

    'Set rst = db.OpenRecordset("select distinct saname from salesinfoforcurryrplan_crosstab")
    blnEXCEL = False

    ' Replace C:\Filename.xls with the actual path and filename
    ' that will be used to save the new EXCEL file into which you
    ' will write the data
    strPathFileName = CurrentProject.Path & "\excelfilestorepsforinputdata" & "ZZ_Plan_Sales.xlsx"

    ' Replace QueryOrTableName with the real name of the table or query
    ' whose data are to be written into the worksheet
    strRecordsetDataSource = "salesinfoforcurryrplan_crosstab"

    ' 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

    ' Create a new EXCEL workbook
    Set xlw = xlx.Workbooks.Add(CurrentProject.Path & "\Master.xltm")

    ' Rename the first worksheet in the EXCEL file to be the first 31
    ' characters of the string in the strRecordsetDataSource variable
    Set xls = xlw.Worksheets(1)
    xls.Name = "Sheet1" 'Trim(Left(strRecordsetDataSource, 31))

    ' Replace A1 with the cell reference of the first cell into which the
    ' headers will be written (blnHeaderRow = True), or into which the data
    ' values will be written (blnHeaderRow = False)
    Set xlc = xls.Range("A23")

    Set dbs = CurrentDb()

    Set rst = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)

    If rst.EOF = False And rst.BOF = False Then
    ' Write the header row to worksheet
    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

    ' copy the recordset's data to worksheet
    xlc.CopyFromRecordset rst
    End If

    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    ' Save and close the EXCEL file, and clean up the EXCEL objects
    Set xlc = Nothing
    Set xls = Nothing
    xlw.SaveAs strPathFileName
    xlw.Close False
    Set xlw = Nothing
    If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing


    Dim lngColumn As Long
    Dim xlx As Object
    Dim xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strPathFileName As String, strWorksheetName As String
    Dim strRecordsetDataSource As String
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    Dim SANAME As String
    Dim tmpltfile As String

    'Set rst = db.OpenRecordset("select distinct saname from salesinfoforcurryrplan_crosstab")
    blnEXCEL = False

    ' Replace C:\Filename.xls with the actual path and filename
    ' that will be used to save the new EXCEL file into which you
    ' will write the data
    strPathFileName = CurrentProject.Path & "\excelfilestorepsforinputdata" & "ZZ_Plan_Sales.xlsx"

    ' Replace QueryOrTableName with the real name of the table or query
    ' whose data are to be written into the worksheet
    strRecordsetDataSource = "salesinfoforcurryrplan_crosstab"

    ' 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

    ' Create a new EXCEL workbook
    Set xlw = xlx.Workbooks.Add(CurrentProject.Path & "\Master.xltm")

    ' Rename the first worksheet in the EXCEL file to be the first 31
    ' characters of the string in the strRecordsetDataSource variable
    Set xls = xlw.Worksheets(1)
    xls.Name = "Sheet1" 'Trim(Left(strRecordsetDataSource, 31))

    ' Replace A1 with the cell reference of the first cell into which the
    ' headers will be written (blnHeaderRow = True), or into which the data
    ' values will be written (blnHeaderRow = False)
    Set xlc = xls.Range("A23")

    Set dbs = CurrentDb()

    Set rst = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)

    If rst.EOF = False And rst.BOF = False Then
    ' Write the header row to worksheet
    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

    ' copy the recordset's data to worksheet
    xlc.CopyFromRecordset rst
    End If

    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    ' Save and close the EXCEL file, and clean up the EXCEL objects
    Set xlc = Nothing
    Set xls = Nothing
    xlw.SaveAs strPathFileName
    xlw.Close False
    Set xlw = Nothing
    If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing


    Click image for larger version. 

Name:	Source file.jpg 
Views:	37 
Size:	63.0 KB 
ID:	39241

  7. #7
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Define a name for A23 range (say XLRange) in Excel template workbook and use this name for the "Range" argument of TransferSpreadsheet method.
    Code:
        '[...]
        Dim XL As Object
        Dim WB As Object
        
        Set XL = CreateObject("Excel.Application")
        Set WB = XL.workbooks.Add("c:\MyBook.xlsx")
        WB.saveas "c:\MyBook2.xlsx"
        WB.Close 0
    
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryMyQueryDef", "c:\MyBook2.xlsx", -1, "XLRange"
        '[...]

  8. #8
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks accestos but it is almost working with the CopyFromRecordset method.

  9. #9
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Just back from holidays and would like to finish off this project before our sales budget is due. I have managed to use the Excel template and load data into the proper locations on the spreadsheet. However, ALL the sales reps are on the same spreadsheet. I need to make a separate spreadsheet(file) for each sales rep and assign their name to the file. I assume this will need a loop. Any assistance would be muchly appreciated.

    Current code:

    Dim lngColumn As Long
    Dim xlx As Object
    Dim xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strPathFileName As String, strWorksheetName As String
    Dim strRecordsetDataSource As String
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    Dim SANAME As String
    Dim tmpltfile As String


    blnEXCEL = False

    ' Replace C:\Filename.xls with the actual path and filename
    ' that will be used to save the new EXCEL file into which you
    ' will write the data
    strPathFileName = CurrentProject.Path & "\excelfilestorepsforinputdata" & "ZZ_Plan_Sales.xlsx"

    ' Replace QueryOrTableName with the real name of the table or query
    ' whose data are to be written into the worksheet
    strRecordsetDataSource = "salesinfoforcurryrplan_crosstab"

    ' 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 = False

    ' Create a new EXCEL workbook
    Set xlw = xlx.Workbooks.Add(CurrentProject.Path & "\Master.xltm")

    ' Rename the first worksheet in the EXCEL file to be the first 31
    ' characters of the string in the strRecordsetDataSource variable
    Set xls = xlw.Worksheets(1)
    xls.Name = "Sheet1" 'Trim(Left(strRecordsetDataSource, 31))

    ' Replace A1 with the cell reference of the first cell into which the
    ' headers will be written (blnHeaderRow = True), or into which the data
    ' values will be written (blnHeaderRow = False)
    Set xlc = xls.Range("A23")

    Set dbs = CurrentDb()

    Set rst = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)

    If rst.EOF = False And rst.BOF = False Then
    ' Write the header row to worksheet
    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

    ' copy the recordset's data to worksheet
    xlc.CopyFromRecordset rst




    'Copy A Range of Data
    Worksheets("Sheet1").Range("j15:q15").Copy

    'PasteSpecial Values Only
    Worksheets("Sheet1").Range("j18").PasteSpecial Paste:=xlPasteValues

    'Clear Clipboard (removes "marching ants" around your original data set)
    'Application.CutCopyMode = False

    End If

    Sheets("Sheet1").Range("e22").Copy (Sheets("Sheet1").Range("e23:e3000"))


    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    ' Save and close the EXCEL file, and clean up the EXCEL objects
    Set xlc = Nothing
    Set xls = Nothing
    xlw.SaveAs strPathFileName

    xlw.Close False

    Set xlw = Nothing
    If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing

    MsgBox "Excel files have been uploaded.", vbOKOnly


    End Sub

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ebrommhead View Post
    <snip> However, ALL the sales reps are on the same spreadsheet. I need to make a separate spreadsheet(file) for each sales rep and assign their name to the file. I assume this will need a loop. Any assistance would be muchly appreciated.
    Yes, you will need to use a loop.

    What is the SQL of the query "salesinfoforcurryrplan_crosstab"?

    How will you get the sales rep names. Is there a sales rep table? How many sales reps?

    Do you want only the sales reps that are in the crosstab query?

  11. #11
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks for your response, Steve.


    Here is the sql of the query:

    TRANSFORM Sum(SalesInfoforCurrYrPlan.SumOfIHDAR_FCAMT1) AS SumOfSumOfIHDAR_FCAMT1
    SELECT SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear, Sum(SalesInfoforCurrYrPlan.SumOfIHDAR_FCAMT1) AS [Total Of SumOfIHDAR_FCAMT1]
    FROM SalesInfoforCurrYrPlan
    GROUP BY SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear
    ORDER BY SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear
    PIVOT SalesInfoforCurrYrPlan.PLMth;


    The sales rep names (SANAME) are in this query and I only want to get the reps that are in the query.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by ebrommhead View Post
    The sales rep names (SANAME) are in this query and I only want to get the reps that are in the query.
    There are several ways to do this.... this is my idea.

    You have a saved query "SalesInfoforCurrYrPlan". In code, I opened a query to get unique "sanames".
    The SQL is
    Code:
        sSQL = "SELECT  DISTINCT sifcpct.saname"
        sSQL = sSQL & " FROM salesinfoforcurryrplan_crosstab AS sifcpct"
        sSQL = sSQL & " ORDER BY sifcpct.saname;"
    Then you fill an array that will be used to filter the query "SalesInfoforCurrYrPlan", which will then be written to individual worksheets.

    I don't have your dB, so I faked it until I made it. (I created a table using the fields in the query)

    Now that there is a "list" of unique sanames, create a loop that:
    1) Creates a new worksheet
    2) Rename the worksheet to the "saname"
    3) open the recordset with only one saname (see below)
    3) write the data to the worksheet

    The SQL of the crosstab query will be in VBA with one additional line - a WHERE statement.
    The SQL would be
    Code:
    TRANSFORM Sum(SalesInfoforCurrYrPlan.SumOfIHDAR_FCAMT1) AS SumOfSumOfIHDAR_FCAMT1
    SELECT SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear, Sum(SalesInfoforCurrYrPlan.SumOfIHDAR_FCAMT1) AS [Total Of SumOfIHDAR_FCAMT1]
    FROM SalesInfoforCurrYrPlan
    WHERE SalesInfoforCurrYrPlan.saname ='" & sa_Array(i) & "'"
    GROUP BY SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear
    ORDER BY SalesInfoforCurrYrPlan.saname, SalesInfoforCurrYrPlan.plant, SalesInfoforCurrYrPlan.CSNAME, SalesInfoforCurrYrPlan.PLYear
    PIVOT SalesInfoforCurrYrPlan.PLMth;
    This is the Test code I used :
    Code:
    Public Sub TestArray()
        Dim d As DAO.Database
        Dim r As DAO.Recordset
    
        Dim rc As Integer  '<<-- record count
        Dim sSQL As String
        Dim sa_Array()       '<<-- array if unique sanames
        Dim i As Integer    '<<-- just a counter
    
        Set d = CurrentDb()
    
        'query to get the unique saNames
        sSQL = "SELECT  DISTINCT sifcpct.saname"
        sSQL = sSQL & " FROM salesinfoforcurryrplan_crosstab AS sifcpct"
        sSQL = sSQL & " ORDER BY sifcpct.saname;"
    
        Set r = d.OpenRecordset(sSQL)
        If Not r.BOF And Not r.EOF Then
            r.MoveLast                '<<-- fill the recordset
            rc = r.RecordCount  ' <<-- number of unique sanames
            r.MoveFirst
    
            ' now we know the number of exements needed in the array
            ReDim sa_Array(rc)
    
            'fill array
            For i = 1 To rc
                sa_Array(i) = r.Fields("saname")
                r.MoveNext
            Next
    
        End If
    
        'clean up
        r.Close
        Set r = Nothing
        Set d = Nothing
    
        ' as a check that the names were added to the array, print to immediate window
        For i = 1 To rc
            Debug.Print sa_Array(i)
        Next
    
    End Sub



    At the 30,000 foot level, the idea is to:
    Pseudo-code
    Code:
    Fill an array with unique sanames
    Create a new workbook
    Loop through the sanames:
      Create new worksheet
      Rename worksheet
      Fill worksheet
    
    Save workbook

    What do you think????

  13. #13
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Again, thanks Steve for your reply. However, I'm not sure how to integrate what you have done with the code that I posted on 8-19-2019 that does create a file within the Excel template but for all reps as one, as opposed to individually.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see the point of the array, it seems like an unnecessary complication. See if this helps, from a project I did last week.

    Code:
        Set db = CurrentDb()
    
        strSQL = "SELECT DISTINCT Account FROM qryVenetianData"
        Set rsVenetianData = db.OpenRecordset(strSQL, dbOpenDynaset)
        strTemplate = "C:\AccessAp\VenetianTeimplate.xlsx"
    
    
        Do While Not rsVenetianData.EOF
            strSQL = "SELECT * " _
                   & "FROM qryVenetianData WHERE Account = " & rsVenetianData!Account
            Set rsCurrent = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    
            strFile = "C:\AccessAp\VenetianData_" & rsVenetianData!Account & ".xlsx"
            FileCopy strTemplate, strFile
            Set xl = CreateObject("excel.application")
            xl.Workbooks.Open (strFile)
            Set xlSheet = xl.Worksheets("VenetianData")
            xlSheet.Select
    
            xlSheet.Range("A3").copyfromrecordset rsCurrent
    
            xl.ActiveWorkbook.Save
            xl.Quit
            Set xl = Nothing
            Set xlSheet = Nothing
    
    
            rsVenetianData.MoveNext
        Loop
    I cut out a bunch of extra stuff automating the spreadsheets, hopefully didn't cut out anything critical.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    ebrommhead is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Location
    Mississauga
    Posts
    28
    Thanks for your post Paul.

    I'm trying to use your code but am getting a run-time error 3075 (missing operator) at the bolded line below. Also, couldn't quite figure out where your "Set reVenetianData" came from, so I used my query name???.

    Note: your code is a note and mine is below. Your help would be appreciated.

    Private Sub Command43_Click()
    Dim strSQL As String
    Dim strTemplate As String


    Set db = CurrentDb()

    'strSQL = "SELECT DISTINCT Account FROM qryVenetianData"
    strSQL = "SELECT DISTINCT SAName FROM salesinfoforcurryrplan_crosstab"
    'Set rsVenetianData = db.OpenRecordset(strSQL, dbOpenDynaset)
    Set rssalesinfoforcurryrplan_crosstab = db.OpenRecordset(strSQL, dbOpenDynaset)
    'strTemplate = "C:\AccessAp\VenetianTeimplate.xlsx"
    strTemplate = CurrentProject.Path & "\Master.xltm"

    'Do While Not rsVenetianData.EOF
    Do While Not rssalesinfoforcurryrplan_crosstab.EOF
    'strSQL = "SELECT * "& "FROM qryVenetianData WHERE Account = " & rsVenetianData!Account
    strSQL = "SELECT * " & "FROM salesinfoforcurryrplan_crosstab WHERE saname = " & rssalesinfoforcurryrplan_crosstab!SANAME
    'Set rsCurrent = db.OpenRecordset(strSQL, dbOpenDynaset)
    Set rsCurrent = db.OpenRecordset(strSQL, dbOpenDynaset)

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-04-2017, 06:54 AM
  2. Replies: 7
    Last Post: 08-29-2014, 12:02 PM
  3. Uploading data from email to database?
    By MikeWP318 in forum Forms
    Replies: 1
    Last Post: 11-01-2011, 03:14 PM
  4. Replies: 1
    Last Post: 05-19-2011, 10:42 PM
  5. Replies: 3
    Last Post: 01-25-2011, 09:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums