Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Why does Column A end up in Column F, and why does C end up in T? Is there a rule around this such as the first row is a header with with the same text in it?</p> <p>Maybe a picture might help.</p> <p>Based on what i can guess, i'd throw each sheet into a RecordSet with meaningful field names (you'll need to reference <code>Microsoft ActiveX Data Objects 2.8 Library</code>) . Once done it will be very easy to append each RecordSet and throw them into a single sheet.</p> <p>You'll need to be able to find the last column and last row in each sheet to do this cleanly so have a look at <a href="https://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro">How can i find the last row...</a></p> <p><strong>Edit...</strong></p> <p>Below is a cleaned up example of how you could do what you need in VBA. The devil is in the details such as empty sheets, and how to handle formulas (this ignores them completely), and how to merge you columns in an appropriate way (again ignored).</p> <p>This has been tested in Excel 2007.</p> <pre><code>Option Explicit Const MAX_CHARS = 1200 Sub MergeAllSheets() Dim rs As Recordset Dim mergedRS As Recordset Dim sh As Worksheet Dim wb As Workbook Dim fieldList As New Collection Dim rsetList As New Collection Dim f As Variant Dim cols As Long Dim rows As Long Dim c As Long Dim r As Long Dim ref As String Dim fldName As String Dim sourceColumn As String Set wb = ActiveWorkbook For Each sh In wb.Worksheets Set rs = New Recordset ref = FindEndCell(sh) cols = sh.Range(ref).Column rows = sh.Range(ref).Row If ref &lt;&gt; "$A$1" Or sh.Range(ref).Value &lt;&gt; "" Then '' This is to catch empty sheet c = 1 r = 1 Do While c &lt;= cols fldName = sh.Cells(r, c).Value rs.Fields.Append fldName, adVarChar, MAX_CHARS If Not InCollection(fieldList, fldName) Then fieldList.Add fldName, fldName End If c = c + 1 Loop rs.Open r = 2 Do While r &lt;= rows rs.AddNew c = 1 Do While c &lt;= cols rs.Fields(c - 1) = CStr(sh.Cells(r, c).Value) c = c + 1 Loop r = r + 1 Debug.Print sh.Name &amp; ": " &amp; r &amp; " of " &amp; rows &amp; ", " &amp; c &amp; " of " &amp; cols Loop rsetList.Add rs, sh.Name End If Next Set mergedRS = New Recordset c = 1 sourceColumn = "SourceSheet" Do While InCollection(fieldList, sourceColumn) '' Just in case you merge a merged sheet sourceColumn = "SourceSheet" &amp; c c = c + 1 Loop mergedRS.Fields.Append sourceColumn, adVarChar, MAX_CHARS For Each f In fieldList mergedRS.Fields.Append CStr(f), adVarChar, MAX_CHARS Next mergedRS.Open c = 1 For Each rs In rsetList If rs.RecordCount &gt;= 1 Then rs.MoveFirst Do Until rs.EOF mergedRS.AddNew mergedRS.Fields(sourceColumn) = "Sheet No. " &amp; c For Each f In rs.Fields mergedRS.Fields(f.Name) = f.Value Next rs.MoveNext Loop End If c = c + 1 Next Set sh = wb.Worksheets.Add mergedRS.MoveFirst r = 1 c = 1 For Each f In mergedRS.Fields sh.Cells(r, c).Formula = f.Name c = c + 1 Next r = 2 Do Until mergedRS.EOF c = 1 For Each f In mergedRS.Fields sh.Cells(r, c).Value = f.Value c = c + 1 Next r = r + 1 mergedRS.MoveNext Loop End Sub Public Function InCollection(col As Collection, key As String) As Boolean Dim var As Variant Dim errNumber As Long InCollection = False Set var = Nothing Err.Clear On Error Resume Next var = col.Item(key) errNumber = CLng(Err.Number) On Error GoTo 0 '5 is not in, 0 and 438 represent incollection If errNumber = 5 Then ' it is 5 if not in collection InCollection = False Else InCollection = True End If End Function Public Function FindEndCell(sh As Worksheet) As String Dim cols As Long Dim rows As Long Dim maxCols As Long Dim maxRows As Long Dim c As Long Dim r As Long maxRows = sh.rows.Count maxCols = sh.Columns.Count cols = sh.Range("A1").End(xlToRight).Column If cols &gt;= maxCols Then cols = 1 End If c = 1 Do While c &lt;= cols r = sh.Cells(1, c).End(xlDown).Row If r &gt;= maxRows Then r = 1 End If If r &gt; rows Then rows = r End If c = c + 1 Loop FindEndCell = sh.Cells(rows, cols).Address End Function </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POMerge Excel Sheets Using VBA
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USMark Nold
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYes it is the identical column headers. The data has to be pasted row-wise but has to be matched according to the column headers. Did i explain clearley?
      singulars
      1. PO
      1. This table or related slice is empty.
    2. COPerfect, reading each sheet into a recordset with the first row as your field names should be a breeze. Merging them shouldn't be too hard either. Getting the columns in the right order may be tricky unless you specify it in your first sheet.
      singulars
      1. PO
      1. USMark Nold
    3. COThank You Very Much Sir.
      singulars
      1. PO
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload