Results 1 to 7 of 7
  1. 07-22-2020, 11:07 AM #1
    Johnny12
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31

    Export to Excel with multiple worksheets (tabs)

    I have my regular (single worksheet one file) Export working and I've only even done exports to excel that has a single worksheet or tab. If I needed it, I would just export to two files then manually combine in one. But I'm sure there is a way to do, just not finding much help from Dr Google. Also searched these forums but getting bad links or expired pages.



    So for the attached code, how would I designate a second worksheet? Then from the point I designate it, I assume its built just like the 1st worksheet? Thanks in advance.



    Code:
    Private Sub Command0_Click()







Dim x1App As Excel.Application
Dim x1Book As Excel.workbook
Dim x1Sheet As Excel.Worksheet
Dim SQL As String
Dim rs1 As DAO.Recordset
Dim i As Integer






'Show User Hourglass - work being performed
DoCmd.Hourglass (True)




'**********************
'Retreive Data
'**********************
'SQL Statement to retrieve data from database
SQL = "Select SourceSystem, Site, HKEY, [STOCK or DTO], [High Limit], lmc, FSC, QtyOutstanding,HQTY, HOD, NSN, Nomenclature, QtyOrdered, UI, UP, ExtendedPrice, Priority, DocNo, QtyOutstanding, CPno, SOD, ShipDate, EDD, Vendor, Status, Comments, Aged, Notes FROM FRCE_REPORT Order by Aged"




'Execute query and populate recordset
Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)










'If no data, don't bother opening excel, just quit
If rs1.RecordCount = 0 Then
  MsgBox "No Data selected for export", vbInformation + vbOKOnly, "No Data Exported"
  GoTo SubExit
End If






'*****************
'BUILD SPREADSHEET
'*****************
'Create an instance of Excel and start building a spreadsheet
 
Set x1App = Excel.Application


x1App.Visible = False
Set x1Book = x1App.Workbooks.Add
Set x1Sheet = x1Book.Worksheets(1)




With x1Sheet
    .Name = "FRCE A009 - WOSR"
    .Cells.Font.Name = "Calibri"
    .Cells.Font.Size = 11
    


'Set Column Widths:
    .Columns("A").ColumnWidth = 9
    .Columns("B").ColumnWidth = 8
    .Columns("C").ColumnWidth = 8
    .Columns("D").ColumnWidth = 11
    .Columns("E").ColumnWidth = 5
    .Columns("F").ColumnWidth = 14
    .Columns("G").ColumnWidth = 40
    .Columns("H").ColumnWidth = 6
    .Columns("I").ColumnWidth = 5
    .Columns("J").ColumnWidth = 11
    .Columns("K").ColumnWidth = 11
    .Columns("L").ColumnWidth = 11
    .Columns("M").ColumnWidth = 16
    .Columns("N").ColumnWidth = 18
    .Columns("O").ColumnWidth = 9
    .Columns("P").ColumnWidth = 12
    .Columns("Q").ColumnWidth = 11
    .Columns("R").ColumnWidth = 11
    .Columns("S").ColumnWidth = 11
    .Columns("T").ColumnWidth = 25
    .Columns("U").ColumnWidth = 14
    .Columns("V").ColumnWidth = 45
    .Columns("W").ColumnWidth = 6
    .Columns("X").ColumnWidth = 6
    .Columns("Y").ColumnWidth = 8
    .Columns("Z").ColumnWidth = 9
    .Columns("AA").ColumnWidth = 45
  
  
 'Set Row Heights:
    .Rows("1").RowHeight = 45
    .Rows("2").RowHeight = 25
    .Rows("3").RowHeight = 0
    
'Format colums:
    .Columns("A").NumberFormat = "@"
    .Columns("A").HorizontalAlignment = xlCenter
    .Columns("A").WrapText = True
    .Columns("A").VerticalAlignment = xlCenter
'
    .Columns("B").NumberFormat = "@"
    .Columns("B").HorizontalAlignment = xlCenter
    .Columns("B").WrapText = True
    .Columns("B").VerticalAlignment = xlCenter
'
'    .Columns("C").NumberFormat = "@"
    .Columns("C").HorizontalAlignment = xlCenter
    .Columns("C").WrapText = True
    .Columns("C").VerticalAlignment = xlCenter
'
    .Columns("D").NumberFormat = "DD/MM/YYYY"
    .Columns("D").HorizontalAlignment = xlCenter
    .Columns("D").WrapText = True
    .Columns("D").VerticalAlignment = xlCenter
'
    .Columns("E").NumberFormat = "#########"
    .Columns("E").HorizontalAlignment = xlCenter
    .Columns("E").WrapText = True
    .Columns("E").VerticalAlignment = xlCenter
'
   .Columns("F").NumberFormat = "000000000"
    .Columns("F").HorizontalAlignment = xlCenter
    .Columns("F").WrapText = True
    .Columns("F").VerticalAlignment = xlCenter
'
    .Columns("G").NumberFormat = "@"
    .Columns("G").HorizontalAlignment = xlLeft
    .Columns("G").WrapText = True
    .Columns("G").VerticalAlignment = xlCenter
'
    .Columns("H").NumberFormat = "#####"
    .Columns("H").HorizontalAlignment = xlCenter
    .Columns("H").WrapText = True
    .Columns("H").VerticalAlignment = xlCenter
'
    .Columns("I").NumberFormat = "$#,##0.00;--$#,##o.oo"
    .Columns("I").HorizontalAlignment = xlCenter
    .Columns("I").WrapText = True
    .Columns("I").VerticalAlignment = xlCenter
'
    .Columns("J").NumberFormat = "$#,##0.00;--$#,##o.oo"
    .Columns("J").HorizontalAlignment = xlRight
    .Columns("J").WrapText = True
    .Columns("J").VerticalAlignment = xlCenter
'


    .Columns("K").HorizontalAlignment = xlRight
    .Columns("K").WrapText = True
    .Columns("K").VerticalAlignment = xlCenter
'
    .Columns("L").NumberFormat = "#####"
    .Columns("L").HorizontalAlignment = xlCenter
    .Columns("L").WrapText = True
    .Columns("L").VerticalAlignment = xlCenter
'
    .Columns("M").NumberFormat = "@"
    .Columns("M").HorizontalAlignment = xlCenter
    .Columns("M").WrapText = True
    .Columns("M").VerticalAlignment = xlCenter
'
    .Columns("N").NumberFormat = "@"
    .Columns("N").HorizontalAlignment = xlCenter
    .Columns("N").WrapText = True
    .Columns("N").VerticalAlignment = xlCenter
'
    .Columns("O").NumberFormat = "@"
    .Columns("O").HorizontalAlignment = xlCenter
    .Columns("O").WrapText = True
    .Columns("O").VerticalAlignment = xlCenter
'
    .Columns("P").NumberFormat = "@"
    .Columns("P").HorizontalAlignment = xlCenter
    .Columns("P").WrapText = True
    .Columns("P").VerticalAlignment = xlCenter
'
    .Columns("Q").NumberFormat = "DD/MM/YYYY"
    .Columns("Q").HorizontalAlignment = xlCenter
    .Columns("Q").WrapText = True
    .Columns("Q").VerticalAlignment = xlCenter
'
    .Columns("R").NumberFormat = "DD/MM/YYYY"
    .Columns("R").HorizontalAlignment = xlCenter
    .Columns("R").WrapText = True
    .Columns("R").VerticalAlignment = xlCenter
'
    .Columns("S").NumberFormat = "DD/MM/YYYY"
    .Columns("S").HorizontalAlignment = xlCenter
    .Columns("S").WrapText = True
    .Columns("S").VerticalAlignment = xlCenter
'
    .Columns("T").NumberFormat = "@"
    .Columns("T").HorizontalAlignment = xlLeft
    .Columns("T").WrapText = True
    .Columns("T").VerticalAlignment = xlCenter
'
    .Columns("U").NumberFormat = "@"
    .Columns("U").HorizontalAlignment = xlCenter
    .Columns("U").WrapText = True
    .Columns("U").VerticalAlignment = xlCenter
    .Columns("U").NumberFormat = "###;[RED]-###;0"
    
    .Columns("V").NumberFormat = "@"
    .Columns("V").HorizontalAlignment = xlLeft
    .Columns("V").WrapText = True
    .Columns("V").VerticalAlignment = xlCenter
    
    .Columns("W").NumberFormat = "@"
    .Columns("W").HorizontalAlignment = xlCenter
    .Columns("W").WrapText = True
    .Columns("W").VerticalAlignment = xlCenter
    .Columns("W").NumberFormat = "###;[RED]-###;0"
     
    .Columns("X").NumberFormat = "@"
    .Columns("X").HorizontalAlignment = xlCenter
    .Columns("X").WrapText = True
    .Columns("X").VerticalAlignment = xlCenter
    .Columns("X").NumberFormat = "###;[RED]-###;0"
    
    .Columns("Y").NumberFormat = "@"
    .Columns("Y").HorizontalAlignment = xlCenter
    .Columns("Y").WrapText = True
    .Columns("Y").VerticalAlignment = xlCenter
   


    .Columns("Z").NumberFormat = "@"
    .Columns("Z").HorizontalAlignment = xlCenter
    .Columns("Z").WrapText = True
    .Columns("Z").VerticalAlignment = xlCenter
   
    
    .Columns("AA").NumberFormat = "@"
    .Columns("AA").HorizontalAlignment = xlLeft
    .Columns("AA").WrapText = True
    .Columns("AA").VerticalAlignment = xlCenter
 






'Add Worksheet Header and Date
    .Range("C1", "V1").Merge
    .Range("C2", "V2").Merge
    .Range("C1").HorizontalAlignment = xlCenter
    .Range("C2").HorizontalAlignment = xlCenter
    .Range("C1").Cells.Font.Size = 20
    .Range("C2").Cells.Font.Size = 16
    .Range("C1").Cells.Font.Bold = True
    .Range("C2").Cells.Font.Bold = True
    .Range("C1").Cells.Font.Name = "Cambria"
    .Range("C2").Cells.Font.Name = "Cambria"
    .Range("C1").Value = "FRCE A009 Weekly Order Status Report (WOSR)"
    .Range("C2").Value = Date
   


 'Build Column Headings
    .Range("A4").Value = "Source System"
    .Range("B4").Value = "Site"
    .Range("C4").Value = "Order Number"
    .Range("D4").Value = "Order Date"
    .Range("E4").Value = "FSC"
    .Range("F4").Value = "NIIN"
    .Range("G4").Value = "Nomenclature"
    .Range("H4").Value = "Order Qty"
    .Range("I4").Value = "UI"
    .Range("J4").Value = "Unit Cost"
    .Range("K4").Value = "Extended Cost"
    .Range("L4").Value = "Priority / RDD"
    .Range("M4").Value = "Document Number"
    .Range("N4").Value = "Cost Point Number"
    .Range("O4").Value = "Qty Ordered"
    .Range("P4").Value = "Qty Outstanding"
    .Range("Q4").Value = "Order Date"
    .Range("R4").Value = "ESD"
    .Range("S4").Value = "EDD"
    .Range("T4").Value = "Vendor"
    .Range("U4").Value = "Supply Status"
    .Range("V4").Value = "Status Comments"
    .Range("W4").Value = "LMC"
    .Range("X4").Value = "Aged"
    .Range("Y4").Value = "Stock DTO"
    .Range("Z4").Value = "High Limit"
    .Range("AA4").Value = "Notes - Steps Taken to Correct"


'Format Columns Heading
    .Range("A4:AA4").HorizontalAlignment = xlCenter
    .Range("A4:AA4").Cells.Font.Bold = True
    .Range("A4:AA4").Interior.Color = RGB(217, 217, 217)
    
    'provide initial value to row counter
    i = 5
    'Loop through recordset and copy data from recordset to sheet
        Do While Not rs1.EOF
        
            .Range("A" & i).Value = Nz(rs1!SourceSystem)
            .Range("B" & i).Value = Nz(rs1!Site)
            .Range("C" & i).Value = Nz(rs1!HKEY, "")
            .Range("D" & i).Value = Nz(rs1!HOD, "")
            .Range("E" & i).Value = Nz(rs1!FSC, "")
            .Range("F" & i).Value = Nz(rs1!NSN, "")
            .Range("G" & i).Value = Nz(rs1!NOMENCLATURE, "")
            .Range("H" & i).Value = Nz(rs1!Hqty, "")
            .Range("I" & i).Value = Nz(rs1!UI, "")
            .Range("J" & i).Value = Nz(rs1!UP, "")
            .Range("K" & i).Value = Nz(rs1!ExtendedPrice, "")
            .Range("L" & i).Value = Nz(rs1!Priority, "")
            .Range("M" & i).Value = Nz(rs1!DocNo, "")
            .Range("N" & i).Value = Nz(rs1!CPno, "")
            .Range("O" & i).Value = Nz(rs1!QtyOrdered, "")
            .Range("P" & i).Value = Nz(rs1!QtyOutstanding)
            .Range("Q" & i).Value = Nz(rs1!SOD)
            .Range("R" & i).Value = Nz(rs1!ShipDate, "")
            .Range("S" & i).Value = Nz(rs1!EDD, "")
            .Range("T" & i).Value = Nz(rs1!Vendor, "")
            .Range("U" & i).Value = Nz(rs1!Status, "")
            .Range("V" & i).Value = Nz(rs1!Comments, "")
            .Range("W" & i).Value = Nz(rs1!LMC, "")
            .Range("X" & i).Value = Nz(rs1!Aged, "")
            .Range("Y" & i).Value = Nz(rs1![STOCK or DTO], "")
            .Range("Z" & i).Value = Nz(rs1![High Limit], "")
            .Range("AA" & i).Value = Nz(rs1!Notes, "")
   
     i = i + 1
     rs1.MoveNext
        
    
    Loop
    
    
'Build Grid lines
    .Range("A4", "AA4").Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:A" & i).Borders(xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:AA" & i).Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:AA" & i).Borders(xlInsideVertical).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:AA" & i).Borders(xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:AA" & i).Borders(xlEdgeBottom).Weight = XlBorderWeight.xlThick
 
 
     
 End With
    
    
SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    x1App.Visible = True
    rs1.Close
    Set rs1 = Nothing
    
  '  Exit Function


'End With








'************************************************************************************
'SECOND TAB
'************************************************************************************






















End Sub
    Reply With Quote Reply With Quote
  2. 07-22-2020, 11:34 AM #2
    Gicu's Avatar
    Gicu
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,115
    Have you tried:
    Code:
    Private Sub Command0_Click()
Dim x1App As Excel.Application
Dim x1Book As Excel.workbook
Dim x1Sheet As Excel.Worksheet
Dim SQL As String
Dim rs1 As DAO.Recordset
Dim i As Integer


'Show User Hourglass - work being performed
DoCmd.Hourglass (True)
'**********************
'Retreive Data
'**********************
'SQL Statement to retrieve data from database
SQL = "Select SourceSystem, Site, HKEY, [STOCK or DTO], [High Limit], lmc, FSC, QtyOutstanding,HQTY, HOD, NSN, Nomenclature, QtyOrdered, UI, UP, ExtendedPrice, Priority, DocNo, QtyOutstanding, CPno, SOD, ShipDate, EDD, Vendor, Status, Comments, Aged, Notes FROM FRCE_REPORT Order by Aged"


'Execute query and populate recordset
Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)


'If no data, don't bother opening excel, just quit
If rs1.RecordCount = 0 Then
  MsgBox "No Data selected for export", vbInformation + vbOKOnly, "No Data Exported"
  GoTo SubExit
End If
'*****************
'BUILD SPREADSHEET
'*****************
'Create an instance of Excel and start building a spreadsheet
 
Set x1App = Excel.Application




x1App.Visible = False
Set x1Book = x1App.Workbooks.Add
Set x1Sheet = x1Book.Worksheets(1)


With x1Sheet
    .Name = "FRCE A009 - WOSR"
    .Cells.Font.Name = "Calibri"
    .Cells.Font.Size = 11


'Set Column Widths:
    .Columns("A").ColumnWidth = 9
    .Columns("B").ColumnWidth = 8
    .Columns("C").ColumnWidth = 8
    .Columns("D").ColumnWidth = 11
    .Columns("E").ColumnWidth = 5
    .Columns("F").ColumnWidth = 14
    .Columns("G").ColumnWidth = 40
    .Columns("H").ColumnWidth = 6
    .Columns("I").ColumnWidth = 5
    .Columns("J").ColumnWidth = 11
    .Columns("K").ColumnWidth = 11
    .Columns("L").ColumnWidth = 11
    .Columns("M").ColumnWidth = 16
    .Columns("N").ColumnWidth = 18
    .Columns("O").ColumnWidth = 9
    .Columns("P").ColumnWidth = 12
    .Columns("Q").ColumnWidth = 11
    .Columns("R").ColumnWidth = 11
    .Columns("S").ColumnWidth = 11
    .Columns("T").ColumnWidth = 25
    .Columns("U").ColumnWidth = 14
    .Columns("V").ColumnWidth = 45
    .Columns("W").ColumnWidth = 6
    .Columns("X").ColumnWidth = 6
    .Columns("Y").ColumnWidth = 8
    .Columns("Z").ColumnWidth = 9
    .Columns("AA").ColumnWidth = 45
  
  
 'Set Row Heights:
    .Rows("1").RowHeight = 45
    .Rows("2").RowHeight = 25
    .Rows("3").RowHeight = 0
    
'Format colums:
    .Columns("A").NumberFormat = "@"
    .Columns("A").HorizontalAlignment = xlCenter
    .Columns("A").WrapText = True
    .Columns("A").VerticalAlignment = xlCenter
'
    .Columns("B").NumberFormat = "@"
    .Columns("B").HorizontalAlignment = xlCenter
    .Columns("B").WrapText = True
    .Columns("B").VerticalAlignment = xlCenter
'
'    .Columns("C").NumberFormat = "@"
    .Columns("C").HorizontalAlignment = xlCenter
    .Columns("C").WrapText = True
    .Columns("C").VerticalAlignment = xlCenter
'
    .Columns("D").NumberFormat = "DD/MM/YYYY"
    .Columns("D").HorizontalAlignment = xlCenter
    .Columns("D").WrapText = True
    .Columns("D").VerticalAlignment = xlCenter
'
    .Columns("E").NumberFormat = "#########"
    .Columns("E").HorizontalAlignment = xlCenter
    .Columns("E").WrapText = True
    .Columns("E").VerticalAlignment = xlCenter
'
   .Columns("F").NumberFormat = "000000000"
    .Columns("F").HorizontalAlignment = xlCenter
    .Columns("F").WrapText = True
    .Columns("F").VerticalAlignment = xlCenter
'
    .Columns("G").NumberFormat = "@"
    .Columns("G").HorizontalAlignment = xlLeft
    .Columns("G").WrapText = True
    .Columns("G").VerticalAlignment = xlCenter
'
    .Columns("H").NumberFormat = "#####"
    .Columns("H").HorizontalAlignment = xlCenter
    .Columns("H").WrapText = True
    .Columns("H").VerticalAlignment = xlCenter
'
    .Columns("I").NumberFormat = "$#,##0.00;--$#,##o.oo"
    .Columns("I").HorizontalAlignment = xlCenter
    .Columns("I").WrapText = True
    .Columns("I").VerticalAlignment = xlCenter
'
    .Columns("J").NumberFormat = "$#,##0.00;--$#,##o.oo"
    .Columns("J").HorizontalAlignment = xlRight
    .Columns("J").WrapText = True
    .Columns("J").VerticalAlignment = xlCenter
'




    .Columns("K").HorizontalAlignment = xlRight
    .Columns("K").WrapText = True
    .Columns("K").VerticalAlignment = xlCenter
'
    .Columns("L").NumberFormat = "#####"
    .Columns("L").HorizontalAlignment = xlCenter
    .Columns("L").WrapText = True
    .Columns("L").VerticalAlignment = xlCenter
'
    .Columns("M").NumberFormat = "@"
    .Columns("M").HorizontalAlignment = xlCenter
    .Columns("M").WrapText = True
    .Columns("M").VerticalAlignment = xlCenter
'
    .Columns("N").NumberFormat = "@"
    .Columns("N").HorizontalAlignment = xlCenter
    .Columns("N").WrapText = True
    .Columns("N").VerticalAlignment = xlCenter
'
    .Columns("O").NumberFormat = "@"
    .Columns("O").HorizontalAlignment = xlCenter
    .Columns("O").WrapText = True
    .Columns("O").VerticalAlignment = xlCenter
'
    .Columns("P").NumberFormat = "@"
    .Columns("P").HorizontalAlignment = xlCenter
    .Columns("P").WrapText = True
    .Columns("P").VerticalAlignment = xlCenter
'
    .Columns("Q").NumberFormat = "DD/MM/YYYY"
    .Columns("Q").HorizontalAlignment = xlCenter
    .Columns("Q").WrapText = True
    .Columns("Q").VerticalAlignment = xlCenter
'
    .Columns("R").NumberFormat = "DD/MM/YYYY"
    .Columns("R").HorizontalAlignment = xlCenter
    .Columns("R").WrapText = True
    .Columns("R").VerticalAlignment = xlCenter
'
    .Columns("S").NumberFormat = "DD/MM/YYYY"
    .Columns("S").HorizontalAlignment = xlCenter
    .Columns("S").WrapText = True
    .Columns("S").VerticalAlignment = xlCenter
'
    .Columns("T").NumberFormat = "@"
    .Columns("T").HorizontalAlignment = xlLeft
    .Columns("T").WrapText = True
    .Columns("T").VerticalAlignment = xlCenter
'
    .Columns("U").NumberFormat = "@"
    .Columns("U").HorizontalAlignment = xlCenter
    .Columns("U").WrapText = True
    .Columns("U").VerticalAlignment = xlCenter
    .Columns("U").NumberFormat = "###;[RED]-###;0"
    
    .Columns("V").NumberFormat = "@"
    .Columns("V").HorizontalAlignment = xlLeft
    .Columns("V").WrapText = True
    .Columns("V").VerticalAlignment = xlCenter
    
    .Columns("W").NumberFormat = "@"
    .Columns("W").HorizontalAlignment = xlCenter
    .Columns("W").WrapText = True
    .Columns("W").VerticalAlignment = xlCenter
    .Columns("W").NumberFormat = "###;[RED]-###;0"
     
    .Columns("X").NumberFormat = "@"
    .Columns("X").HorizontalAlignment = xlCenter
    .Columns("X").WrapText = True
    .Columns("X").VerticalAlignment = xlCenter
    .Columns("X").NumberFormat = "###;[RED]-###;0"
    
    .Columns("Y").NumberFormat = "@"
    .Columns("Y").HorizontalAlignment = xlCenter
    .Columns("Y").WrapText = True
    .Columns("Y").VerticalAlignment = xlCenter
   




    .Columns("Z").NumberFormat = "@"
    .Columns("Z").HorizontalAlignment = xlCenter
    .Columns("Z").WrapText = True
    .Columns("Z").VerticalAlignment = xlCenter
   
    
    .Columns("AA").NumberFormat = "@"
    .Columns("AA").HorizontalAlignment = xlLeft
    .Columns("AA").WrapText = True
    .Columns("AA").VerticalAlignment = xlCenter
 


'Add Worksheet Header and Date
    .Range("C1", "V1").Merge
    .Range("C2", "V2").Merge
    .Range("C1").HorizontalAlignment = xlCenter
    .Range("C2").HorizontalAlignment = xlCenter
    .Range("C1").Cells.Font.Size = 20
    .Range("C2").Cells.Font.Size = 16
    .Range("C1").Cells.Font.Bold = True
    .Range("C2").Cells.Font.Bold = True
    .Range("C1").Cells.Font.Name = "Cambria"
    .Range("C2").Cells.Font.Name = "Cambria"
    .Range("C1").Value = "FRCE A009 Weekly Order Status Report (WOSR)"
    .Range("C2").Value = Date
  


 'Build Column Headings
    .Range("A4").Value = "Source System"
    .Range("B4").Value = "Site"
    .Range("C4").Value = "Order Number"
    .Range("D4").Value = "Order Date"
    .Range("E4").Value = "FSC"
    .Range("F4").Value = "NIIN"
    .Range("G4").Value = "Nomenclature"
    .Range("H4").Value = "Order Qty"
    .Range("I4").Value = "UI"
    .Range("J4").Value = "Unit Cost"
    .Range("K4").Value = "Extended Cost"
    .Range("L4").Value = "Priority / RDD"
    .Range("M4").Value = "Document Number"
    .Range("N4").Value = "Cost Point Number"
    .Range("O4").Value = "Qty Ordered"
    .Range("P4").Value = "Qty Outstanding"
    .Range("Q4").Value = "Order Date"
    .Range("R4").Value = "ESD"
    .Range("S4").Value = "EDD"
    .Range("T4").Value = "Vendor"
    .Range("U4").Value = "Supply Status"
    .Range("V4").Value = "Status Comments"
    .Range("W4").Value = "LMC"
    .Range("X4").Value = "Aged"
    .Range("Y4").Value = "Stock DTO"
    .Range("Z4").Value = "High Limit"
    .Range("AA4").Value = "Notes - Steps Taken to Correct"




'Format Columns Heading
    .Range("A4:AA4").HorizontalAlignment = xlCenter
    .Range("A4:AA4").Cells.Font.Bold = True
    .Range("A4:AA4").Interior.Color = RGB(217, 217, 217)
    
    'provide initial value to row counter
    i = 5
    'Loop through recordset and copy data from recordset to sheet
        Do While Not rs1.EOF
        
            .Range("A" & i).Value = Nz(rs1!SourceSystem)
            .Range("B" & i).Value = Nz(rs1!Site)
            .Range("C" & i).Value = Nz(rs1!HKEY, "")
            .Range("D" & i).Value = Nz(rs1!HOD, "")
            .Range("E" & i).Value = Nz(rs1!FSC, "")
            .Range("F" & i).Value = Nz(rs1!NSN, "")
            .Range("G" & i).Value = Nz(rs1!NOMENCLATURE, "")
            .Range("H" & i).Value = Nz(rs1!Hqty, "")
            .Range("I" & i).Value = Nz(rs1!UI, "")
            .Range("J" & i).Value = Nz(rs1!UP, "")
            .Range("K" & i).Value = Nz(rs1!ExtendedPrice, "")
            .Range("L" & i).Value = Nz(rs1!Priority, "")
            .Range("M" & i).Value = Nz(rs1!DocNo, "")
            .Range("N" & i).Value = Nz(rs1!CPno, "")
            .Range("O" & i).Value = Nz(rs1!QtyOrdered, "")
            .Range("P" & i).Value = Nz(rs1!QtyOutstanding)
            .Range("Q" & i).Value = Nz(rs1!SOD)
            .Range("R" & i).Value = Nz(rs1!ShipDate, "")
            .Range("S" & i).Value = Nz(rs1!EDD, "")
            .Range("T" & i).Value = Nz(rs1!Vendor, "")
            .Range("U" & i).Value = Nz(rs1!Status, "")
            .Range("V" & i).Value = Nz(rs1!Comments, "")
            .Range("W" & i).Value = Nz(rs1!LMC, "")
            .Range("X" & i).Value = Nz(rs1!Aged, "")
            .Range("Y" & i).Value = Nz(rs1![STOCK or DTO], "")
            .Range("Z" & i).Value = Nz(rs1![High Limit], "")
            .Range("AA" & i).Value = Nz(rs1!Notes, "")
   
     i = i + 1
     rs1.MoveNext
        
    
    Loop
    
    
'Build Grid lines
    .Range("A4", "AA4").Borders(xlEdgeTop).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:A" & i).Borders(xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:AA" & i).Borders(xlEdgeRight).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:AA" & i).Borders(xlInsideVertical).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:AA" & i).Borders(xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous
    .Range("A4:AA" & i).Borders(xlEdgeBottom).Weight = XlBorderWeight.xlThick


     
 End With
'************************************************************************************
'SECOND TAB
'************************************************************************************


Set x1Sheet = x1Book.Worksheets.Add (After:1Book.Worksheets(1))
With x1Sheet










End IF








SubExit:
On Error Resume Next
    DoCmd.Hourglass False
    x1App.Visible = True
    rs1.Close
    Set rs1 = Nothing
    
  








End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/
    Reply With Quote Reply With Quote
  3. 07-22-2020, 11:37 AM #3
    pbaldy's Avatar
    pbaldy
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,521
    If you know their names, this is how I do it:

    Code:
      Set xlSheet = xl.Worksheets("Limo")
  xlSheet.Select

  do some stuff

  Set xlSheet = xl.Worksheets("Bus")
  xlSheet.Select

  do some other stuff
    I'm using a template with the names. Your method will also work to choose a sheet by number.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com
    Reply With Quote Reply With Quote
  4. 07-23-2020, 10:22 AM #4
    Johnny12
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31
    Thank you for the assistance. The below code worked so I can now do multiple tabs, but another problem popped up. See next post.

    Code:
    Set x1Sheet = x1Book.Worksheets.Add(After:=x1Book.Worksheets(1))



With x1Sheet
   .Name = "Pending"
   .Cells.Font.Name = "Calibri"
    .Cells.Font.Size = 11
    Reply With Quote Reply With Quote
  5. 07-23-2020, 10:45 AM #5
    Gicu's Avatar
    Gicu
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,115
    Glad to hear you got it going!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/
    Reply With Quote Reply With Quote
  6. 07-23-2020, 10:49 AM #6
    Johnny12
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31
    Click image for larger version.  Name: error1.PNG  Views: 9  Size: 4.3 KB  ID: 42501Click image for larger version.  Name: error2.PNG  Views: 9  Size: 3.0 KB  ID: 42502

    Welp, spoke a little too soon The above works, but now I'm having trouble changing the data set. if I cut and past the code for the first tab into the area for the second tab, it works just fine. Of course it populates the exact same data. But I need to pull data from different tables. First one comes from FRCE_REPORT, the second tab, which has the exact same colums rows and buld, is from FRCE_REPORT_STOCK I get the below error. I'm including the full code as well again to see where it errors.



    NEVERMIND - I forgot I changed one of the field names. That was causing the error. Everything works now
    Reply With Quote Reply With Quote
  7. 07-23-2020, 11:32 AM #7
    pbaldy's Avatar
    pbaldy
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,521
    Glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com
    Reply With Quote Reply With Quote
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Export to Excel; One query, multiple tabs by unit
    By catluvr in forum Import/Export Data
    Replies: 10
    Last Post: 04-05-2018, 05:04 PM
  2. VBA to rename tabs in multiple worksheets
    By mindbender in forum Modules
    Replies: 2
    Last Post: 02-12-2015, 04:03 PM
  3. regularly import/export diff records to multiple excel worksheets
    By crobaseball in forum Access
    Replies: 3
    Last Post: 03-16-2014, 08:09 PM
  4. Export Report to existing workbook with multiple new tabs in excel
    By Chatholo in forum Import/Export Data
    Replies: 2
    Last Post: 05-16-2013, 07:43 PM
  5. Export Access table to multiple excel workbooks with multiple tabs (sheets)
    By captdkl02 in forum Access
    Replies: 12
    Last Post: 12-17-2012, 12:47 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Other Forums: Microsoft Office Forums