Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Let me help you with the CSV looping for now since this is rather hard for a beginner. I'm sure you will figure out how to test for a value in row 8. If not, you can always ask for more help!</p> <p>In order to do so, you will have to use the Microsoft Scripting Runtime.</p> <p>I suggest placing all of the csv file you want to open in the same directory, and only those to avoid potential problems.</p> <p>Open a new workbook and go to the VBE (ALT + F11). Create a new module. Click in this new module, then go to Tools > References> Microsoft Scripting Runtime. This will let it know it will have to use that module and its objects.</p> <p>Save the workbook as an macro-enabled workbook (.xls or .xslm for newer versions) in the same directory as your CSV (or somewhere else...)</p> <p>Then start coding:</p> <pre><code>Sub Import_all_Csv() ' Reference Needed: Microsoft Scripting Runtime ' Dim some pointers to know what objects you will be manipulating thereafter Dim MyWs, CSV As Worksheet Set MyWs = ActiveSheet ' Meaning you will have to run the macro from the spreadsheet you want to export to. Feel free to replace that Dim wbCSV As Workbook ' Those are the objects that belong to the reference Microsoft Scripting Runtime Dim oFSO As FileSystemObject Dim oFld As Folder Dim oFile As File Dim File As String ' Initialize the FileSystemObject Set oFSO = New FileSystemObject ' That will only work on windows so I'm adding an error handler to ignore it if need be On Error Resume Next ChDir ThisWorkbook.Path On Error GoTo 0 ' I'm asking VBA to throw an error now ' Dialog box to select the first csv file (this will let you choose another directory everytime) File = Application.GetOpenFilename("Comma Separated Values File (*.csv*), *.csv*") If File = "False" Then Exit Sub ' Quit the macro if user canceled Else ' Else get the path of the parent folder of that file Set oFld = oFSO.GetFolder(oFSO.GetParentFolderName(File)) End If ' Go through each file in that folder For Each oFile In oFld.Files ' Only open the CSV files If oFile.Type = "Microsoft Excel Comma Separated Values File" Then ' Open it and set the first sheet (There is only one anyway) Set wbCSV = Workbooks.Open(oFile) Set CSV = wbCSV.Sheets(1) ' ============================ ' Do what you want to do Here ' THIS IS A PLACEHOLDER ' Example to copy value of H8 in the CSV file to A2 the destination worksheet so you can see how to point to the correct cells in both files MyWs.cells(1,2).value = wCSV.cells(8,8).value ' End of what you want to do ' ============================ ' Close the CSV file without savings changes before going through the next one wbCSV.Close False End If Next oFile End Sub </code></pre> <p>I hope this helps! Good luck learning more VBA!</p> <p>Best, Julien</p>
    singulars
    1. This table or related slice is empty.
    1. POExtracting certain cells from excel files in a specified directory
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJulien Marrec
    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
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COUsing `Dir` with a wildcard is much more efficient that testing the FileType of every file in the directory. See http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba/10382861#10382861
      singulars
      1. PO
      1. USbrettdj
    2. COOh, that seems pretty cool. I'll definitely look into it (works on Mac?). Thanks!
      singulars
      1. PO
      1. USJulien Marrec
    3. CONot a MAC VBA expert so not sure :)
      singulars
      1. PO
      1. USbrettdj
 

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