Databases
StackOverflow2013
Postsdbo
POVB Macro - Create CSV file by copying relevant data from different spreadsheets
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVB Macro - Create CSV file by copying relevant data from different spreadsheets
    primarykey
    data
    text
    <p>i don't write on VB, but i need a script to do some work for me. if someone could help... I have a folder1 with excel files in it. I have a Additional1 sheet with some additional data.</p> <ol> <li>I need a macro to go thru folder1 reading files and copying certain columns into a CSV file (can be a new or using template) with certain headers in first row. </li> <li>Then looking by a cat number in Additional1 copy some additional data from certain columns</li> <li>and then saving this new CSV under the name which can be found under a specific header in the excel file which we are reading from folder1.</li> </ol> <p>here is the content of one of the files from folder1</p> <pre><code>Aritst Year Manufacturer UPC Catalog No Track # Track Name Blackfield 2007 8.02645E+11 KSCOPE126M 1 Once Blackfield 2007 8.02645E+11 KSCOPE126M 2 Bla People Blackfield 2007 8.02645E+11 KSCOPE126M 3 Miss U Blackfield 2007 8.02645E+11 KSCOPE126M 4 Christenings </code></pre> <p>Say I need only</p> <p>A, B, D and F columns copied to</p> <p>K, E, A and AD </p> <p>of the CSV file correspondingly (i.e. CSV Column A will contain data of column D of opened spreadsheet - in above example Catalog No)</p> <p>here is the code I've got:</p> <pre><code>Sub Convert_to_Digi() ' First delete existing data Dim LastRow As Long Dim SrcWkb As Workbook Dim StartRow As Long Dim wkbname As Variant Dim xlsFiles As Variant Dim MyRange As Variant Dim NewName As Variant StartRow = 2 ' Get the workbooks to open xlsFiles = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True) If VarType(xlsFiles) = vbBoolean Then Exit Sub ' Loop through each workbook and copy the data to this CSV For Each wkbname In xlsFiles Set SrcWkb = Workbooks.Open(Filename:=wkbname, ReadOnly:=True) MyRange = Sheets("export_label_conf").Range("A:A") LastRow = Application.WorksheetFunction.CountA(MyRange) Sheets("export_label_conf").Select NewName = Cells(3, 2) &amp; ".csv" If LastRow &gt;= StartRow Then ' copy column D data With SrcWkb.Worksheets("export_label_conf") .Range(.Range("D2"), .Range("D").LastRow).Copy SrcWkb.Worksheets("export_label_conf").Select Range("D2:D" &amp; LastRow).Select Selection.Copy ' paste into CSV template file Workbooks.Open Filename:="C:\DIGITAL\template.csv", ReadOnly:=False Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With ' and save template as new CSV with barcode as name Name = CurDir &amp; "\" &amp; NewName ActiveWorkbook.SaveAs Filename:= _ Name, FileFormat:= _ xlCSV, CreateBackup:=False End If SrcWkb.Close Next wkbname End Sub </code></pre> <p>I'm stuck with copying multiple columns at once into CSV file... and generally not sure if the script is written correctly =) can someone give me a hand on this please?</p> <hr> <p><strong>UPDATE 28.02.11 11:23</strong></p> <p>epic fail on trying to implement vlookup =)</p> <p>'vlookup additional data from a spreadsheet</p> <pre><code> Dim FndStr As String Dim FndVal As Range Dim addWkb As Variant Dim AddInfo As String ' copy column E FndStr = MyRange.Columns(12).Value Set addWkb = Workbooks.Open(Filename:="C:\DIGITAL\Snapper Owned Licensed Catalogue.xls", ReadOnly:=True) Set FndVal = Columns("B:B").Find(What:=FndStr, LookAt:=xlWhole) If FndVal Is Nothing Then MsgBox "ID not found!!" Else 'get value of column D AddInfo = FndVal.Offset(0, 3).Value End If ' paste into CSV template file, ADDITIONAL INFO into AO column csvWkb.ActiveSheet.Cells(2, 41).PasteSpecial Paste:=AddInfo, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False </code></pre> <p>i pasted this before "and save template as new CSV with barcode as name" in Chris's code edit... Help please? how do i loop thru the column and vlookup each value?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USElen
    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. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    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