Combining information

Blunty67 · Dec 1st 2021

Afternoon team, I just after some help, I would like to automate a task where I can add some data to a SS and have it combine some detail for me. To date I have been using a formula to Concatenate the data, but there is still an amount of manual effort involved. I have a basic knowledge of VBA and know it is possible just not where to start.

List of names will be in columns A - D, rows down until empty

A1 – A99 B1-B99 C1 – C99 D1-D99

BBH-Name 11.22.33.44 This is a note Extra Note

I need the combine the columns with some other info so that the output looks like below:

So essentially the column A detail is in bold to show where it needs to go.

object Host "BBH-Name" {

notes = "This is a note, Extra Note"

address = "11.22.33.44"

import "generic-host"

vars.type = "Printer"

check_command = "hostalive"

vars.snmp_printer["Toner"] = {snmp_printer_consum = ""}

vars.snmp_printer["Messages"] = {snmp_printer_messages = ""}

vars.snmp_printer["Pagecount"] = {snmp_printer_pagecount = ""}

vars.snmp_printer["Trays"] = {snmp_printer_trays = ""}

}

The output needs to be put in a cell, on a separate sheet so I can copy and paste into a text file.

Thanks in advance for any assistance you can provide.

**royUK** · Dec 1st 2021

Attach an example workbook

Blunty67 · Dec 2nd 2021

Hi Roy,

I ended up using this code

Code

Sub GrabData()

Dim ReadRowCount As Long
Dim WriteRowCount As Long
Dim InputText As String
Dim ReadSheet As Worksheet
Dim WriteSheet As Worksheet
Set ReadSheet = ActiveWorkbook.Sheets(1)
Set WriteSheet = ActiveWorkbook.Sheets(2)
ReadRowCount = 2 'Do not count Headers
WriteRowCount = 2 ' Do not count Headers
While Len(ReadSheet.Range("A" & ReadRowCount).Value) > 0 ' must be text in column A
InputText = "object Host """ & ReadSheet.Range("A" & ReadRowCount).Value & """ {" & vbNewLine
InputText = InputText & "address = """ & ReadSheet.Range("B" & ReadRowCount).Value & """" & vbNewLine
InputText = InputText & "notes = """ & ReadSheet.Range("C" & ReadRowCount).Value & ", "
InputText = InputText & ReadSheet.Range("D" & ReadRowCount).Value & """" & vbNewLine
InputText = InputText & "import """ & "generic-host" & """" & vbNewLine
InputText = InputText & "vars.Type = """ & "Printer" & """" & vbNewLine
InputText = InputText & "check_command = """ & "hostalive" & """" & vbNewLine
InputText = InputText & "vars.snmp_printer [""" & "Toner" & """] = {snmp_printer_consum = """"}" & vbNewLine
InputText = InputText & "vars.snmp_printer [""" & "Messages" & """] = {snmp_printer_messages = """"}" & vbNewLine
InputText = InputText & "vars.snmp_printer [""" & "Pagecount" & """] = {snmp_printer_pagecount = """"}" & vbNewLine
InputText = InputText & "vars.snmp_printer [""" & "Trays" & """] = {snmp_printer_trays = """"}" & vbNewLine
InputText = InputText & "}"
WriteSheet.Range("A" & WriteRowCount).Value = InputText
ReadRowCount = ReadRowCount + 1
WriteRowCount = WriteRowCount + 1
Wend

End Sub

Display More

Which I have manged to get working now, the issue I am now having is the output on sheet 2 is fine, but when I copy and paste it adds additional "" to each line.

I then found some code on the web to remove and export the output to a txt file, but when I copy and paste it in to the program I am using it adds them again

Combining information

Participate now!

Share