Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862

    All I need now is to make it use only one excel sheet and paste the records inside
    This is why I suggested the use of a temp table. With a temp table you could employ Excel's CopyFromRecordset method. I mentioned the CopyFromRecordset in post #7. So, you might be able to use a form's recordsetclone, but a temp table may be cleaner.

  2. #17
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,
    Thanks, but the mayor drawback I have is that I need to paste the next record in the excel sheet on the right side of the last one.
    so it would look like this:

    Record set1 || Record Set2 || Record Set3 (etc. etc.)

    I need to find another way to paste the records sets.

    Greetings.

  3. #18
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I need to paste the next record in the excel sheet on the right side of the last one.
    Then a temp table may not be the way to go. You can define ranges using CopyFromRecordset.

    Here is another example
    https://www.accessforums.net/macros/...tml#post240852

    Here is MSDN reference
    https://msdn.microsoft.com/en-us/lib.../ff839240.aspx

  4. #19
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    If I can have the below code to create for each record set a new sheet, then I would be more than happy :-)
    It is freaking hard to do what they asked me, so, I could run later from excel some code to combine all those sheets into one sheet and that would still be a time saver :-).

    Any chance the below code can be adopted?

    Code:
     Private Sub query_results1()
    Dim sFile As String
    Dim stDocName As String
    sFile = "C:\Documents\Query_1.xls"
    stDocName = "Query_1"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query1", "C:\Documents\Query_1.xls", True
    Dim xlApp As Object
    Dim xlSheet As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    xlApp.Visible = True
    With xlSheet
    .Range("A1").Value = "QUERY DATE"
    .Range("B1").Value = "BRANCH NO"
    .Range("C1").Value = "ACCOUNT NO"
    .Range("D1").Value = "PRODUCTS"
    .Range("A1:D1").Font.Bold = True
    .Range("A1:D1").EntireColumn.AutoFit
    End With
    End Sub
    Greetings.

  5. #20
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If you want to export query1, use the example I provided you in post #18. Although, instead of opening a form's Recordset Clone, open your query1. It is preferable in your case (I believe) to use a query object, anyway.

    Code:
    dim db as dao.database
    set db = currentdb
    dim rs as dao.recordset
    set rs = db.openrecordset("query1", dbopensnapshot)
    
    if rs.eof = true then
    set rs = nothing
    set db = nothing
    exit sub
    end if
    Then use the second code snippet in the link to create the spreadsheet and export the recordset.
    Code:
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
        With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").Select
            
            .ActiveSheet.Range("A2").CopyFromRecordset rs
            
            For i = 1 To rs.Fields.Count
                xlApp.ActiveSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
            Next i
            xlApp.Cells.EntireColumn.AutoFit
        End With

  6. #21
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,
    I get runtime error 3061, to few parameter. expected 4

    I guess it does not get the query criteria in the query and it sticks there.

    Greetings.

  7. #22
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    When I get runtime errors, I will use the debugger by clicking OK in the prompt. This way I can get the information needed to understand what went wrong. If I had to guess, I would say your query1 uses dynamic parameters based on another object, like a form. This type of query object does not play well with DAO recordset objects.

  8. #23
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    HI,

    You are right, it takes the criteria from the form to build the query.
    You can see how I build it in post nr 12.

    this is the criteria in the query disign view:

    Code:
    Between [Forms]![frm_main_form]![from_date_txt] And [Forms]![frm_main_form]![to_date_txt]
    Code:
    [Forms]![frm_main_form]![branch_txt]
    Code:
    [Forms]![frm_main_form]![account_txt]
    (the below code runs the loop and each time it passes the record to the variables that are used to run the query)


    so this code gets the creates the criteria for the query and pass it to the form unbound fields:
    Code:
    Private Sub btn_batch_process_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim from_date As String
    Dim to_date As String
    Dim branch As String
    Dim account As String
    Dim strSQL As String
    i = 0
    Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT from_date, to_date, branch, account FROM tbl_batch_process", dbOpenDynaset)
    
        rs.MoveFirst
    Do Until rs.EOF
    from_date_txt = rs![from_date]
    to_date_txt = rs![to_date]
    branch_txt = rs![branch]
    account_txt = rs![account]
    
    'Now do something with these variables
    
    If rs.EOF = True Then
        Set rs = Nothing    'Release machine's memory
            Set db = Nothing
        Exit Sub
    End If
    i = i + 1
    Check_first_branch_nr_Click
        rs.MoveNext
        
    Loop
    rs.Close 'Close recordset when you are done.
    Set rs = Nothing
    Set db = Nothing
    End Sub
    And this is the code that outputs the query to excel:
    Code:
     Private Sub query_results1()
    Dim sFile As String
    Dim stDocName As String
    sFile = "C:\Documents\Query_1.xls"
    stDocName = "Query_1"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query1", "C:\Documents\Query_1.xls", True
    Dim xlApp As Object
    Dim xlSheet As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    xlApp.Visible = True
    With xlSheet
    .Range("A1").Value = "QUERY DATE"
    .Range("B1").Value = "BRANCH NO"
    .Range("C1").Value = "ACCOUNT NO"
    .Range("D1").Value = "PRODUCTS"
    .Range("A1:D1").Font.Bold = True
    .Range("A1:D1").EntireColumn.AutoFit
    End With
    End Sub
    For me it would be already good if the above code could be adjusted to put it all in the same workbook and create for each time the query has a new recordset, to add one more sheet to the workbook to it and paste it there.

    Greetings.

    Greetings.

  9. #24
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    so this code gets the creates the criteria for the query and pass it to the form unbound fields:
    The code you posted there for your query does not make much sense to me. It seems to me that you are trying to make this query work and I do not see how it can. One of the most difficult things to do when developing an app is to conceptualize the problem and then break off small bits. It becomes more difficult when you do not have a clear picture how to manage each of the steps.

    You can certainly use transferspreadsheet or outputto for your export. However, you cannot export a wish. You need something to export. I suggest you start with some hardcoded values for your where criteria. Place them in a query object or directly into an SQL string.

    It seems to me that you are under pressure to get past the finish line. However, focusing on the prize is not going to get you any closer to the finish line.

    Go to the query designer. Maybe start with the following
    SELECT from_date, to_date, branch, account FROM tbl_batch_process

    Then add some where criteria. Type the values. Hard code the criteria. This is how I would do it.

    Now use this query with the hardcoded WHERE clause in you export. Then go back to the query and hard code another set of values. Export that object. After doing this a few times, you should have a manual process of your big picture. I would then worry about automating the WHERE clause. I would not worry about automating anything else. Break off a small piece and automate the WHERE clause. Maybe debug.print the result in the immediate window.

    If you are not interested in doing any of this, please let me know. I, too, have work to do.

  10. #25
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    I did all the above and I got the results I need, but the problem is passing the results of the query to the excel sheet one by one.
    I'm guessing that maybe I should create a variable for the query results that I can pass later to the excel sheet.
    I will give it another try and see if I can manage to change it all and after all I will not need to create so many sheets.

    Greetings and Merry Xmas.

  11. #26
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ken Snell has a page on exporting form Access to Excel. Maybe it will help you:

    Exporting to EXCEL Workbook Files
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

  12. #27
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by megatronixs View Post
    ...I did all the above and I got the results I need...
    Can you post the SQL from one of the queries you created? Be sure to include the WHERE clause you hardcoded.

  13. #28
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    At the end I move this to excel. It is just not working. I managed to finalize it for 80%, but the same code will just run once not and the second time it will run.

    I hope some one will be able to make use of the code I managed so far.

    Greetings and all the best wishes in the new year :-)

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

Similar Threads

  1. Replies: 3
    Last Post: 03-10-2013, 07:04 AM
  2. Replies: 1
    Last Post: 06-15-2012, 10:47 AM
  3. create table using something like loop query
    By learning_graccess in forum Queries
    Replies: 20
    Last Post: 04-18-2012, 09:52 AM
  4. Replies: 1
    Last Post: 12-01-2010, 11:01 AM
  5. Loop a table to use field in query
    By jdubp in forum Programming
    Replies: 0
    Last Post: 03-04-2008, 11:48 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