VBS Macro to Reformat Data For Import

Hi,

Where I work, we receive spreadsheets that come formatted in a certain way. I have to take these spreadsheets and manually go through each one to put them in a different format to import in to our system. I know there has to be an easier way to do this using VBS in Excel. I'm terrible with VBS so I was wondering if someone could help me. I know from a previous VBS macro that I received help with that I can have it run a File Open dialog box and select multiple files. I would like a macro to allow me to select multiple excel files and have it take each entry from the spreadsheets and place them in the formatted spreadsheet. This would make my import process not so much of a headache as it has been in the past.

I've attached a sample of the data file and a sample of what I would like the final results to be.

Thanks for any help in advance.

Hi

the customer serial number and Sensus Serial Number are in different columns between the Test Data sheets in the 2 files. which is correct?

rylo

Sorry about that. I meant to remove that 2nd spreadsheet. The Data Sample file has it in the correct column. When I do the process manually I have to move that column so I can use vlookup for everything else. Sorry for the confusion.

Hi

This isn't complete (way too many output columns!!!), but it should get you going. It assumes that the macro is in the output workbook, the output sheet is "Sheet 1" and it already exists in the output workbook along with the column headings.

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = ThisWorkbook.Sheets("Sheet 1")
  
  FileNames = Application.GetOpenFilename(MultiSelect:=True)
  
  For i = LBound(FileNames) To UBound(FileNames)
    Set thisone = Workbooks.Open(FileNames(i))
    For j = 7 To Cells(Rows.Count, 1).End(xlUp).Row
      outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      OutSH.Cells(outrow, 1).Value = "ELEC"
      OutSH.Cells(outrow, 2).Value = WorksheetFunction.Trim(Cells(j, 2))
      OutSH.Cells(outrow, 3).Value = Format(Cells(j, 4), "YYYYMMDD")
      OutSH.Cells(outrow, 4).Value = 0
      OutSH.Cells(outrow, 7).Value = Format(Cells(j, "H"), "00.00")
      OutSH.Cells(outrow, 8).Value = Format(Cells(j, "G"), "00.00")
      OutSH.Cells(outrow, 9).Value = Format(Cells(j, "F"), "00.00")
      OutSH.Cells(outrow, 13).Value = "M"
      OutSH.Cells(outrow, 15).Value = "MAN"
      OutSH.Cells(outrow, 16).Value = Cells(j, "E").Value
    
    
    Next j
    ActiveWorkbook.Close savechanges:=False
  Next i
End Sub

rylo

Awesome. I'll play with the code and see if I can get it working. Thank you for your help.

Thank you so much for the help. I was able to learn by your example and complete the rest of the VB code. Now I can import these without it being such a headache.

I have one more problem that I need help with. We have another company that send us the files to import in a text file. The text file is comma delimited and comes in correctly formatted when you use the Import Data From Text feature in Excel. However, I can only import one text file at a time. Is there a way (maybe not even needing VBA) to import all of the text files at once? I've search for "import multiple text file" and tried a few different macros, but none have worked correctly.

I've attached an example text file.

Thanks in advance again.

Hi

Not that I know about. What would be simpliest would be to put all the files in the same location, then do a loop to import a file, process it, close it, then move to the next file. Record a macro to do the import, and then just butcher it to put it inside a loop to process all the files.

HTH

rylo