Convert excel data to vba code

[tester3x]

I am trying to get data from a generic excel file put in a line of VBA code. I get the data online and it comes in various amounts. I would like to convert the data in batches of various sizes, whatever the amount of data I have at that time. There are 2 pieces of data that go in the vba line. I want the output to be in a way that i can copy/paste it into the file i need it to be in. This is for personal use so nothing fancy needs done.

I was thinking I would paste the data into excel and click a button to convert however much I had to the proper vba syntax.

In the generic excel file I need the name, column E, in this set of data. I also need the data in column I. I can format the data however it needs to be easy for whoever might help me with this.

The format of the conversion is in the picture I uploaded. The data in column e will be put in the " " with text. The data in column I will be put where the number is.

{["name"] = "ColumnEgoesherewithnospaces",["id"] = column I goes here,["button"] = nil},

This would be the finished code...

{["name"] = "SoulbindCacheOpener_Generic_unneeded_name",["id"] = 193484,["button"] = nil},

I'm sure I've butchered my explanation but I know you will ask if you need to.

Thanks for your help.

[k1dr0ck]

try

Please Login or Register  to view this content.

[tester3x]

Ok, i see the code but im way greener than that. How do i run it and all?

Its also telling me my macros are disabled which they are not. I have the macros setting on trust everything.

[tester3x]

oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh oh...

I figured it out. It works like a charm. I just need to put it to a button so i dont have to open the developers window. I dont know if there is a way to run it from the sheet without a button.

Thank you sooooooooo much.

[k1dr0ck]

you run it without a button by pressing ALT+F8 then select the "convert" macro

you can also rename it to whatever suits you Sub convert() to Sub convert_tester3x()

[tester3x]

I did make a button to run it. I also made a few others. I'll link it here again cuz I need help with another aspect of it.

Another format that I will use to get the values for the script is:

h t t p s:**
www wowhead com*item=21216*smokywood-pastures-extra-special-gift

The ID number is, well obviously the only numbers in that line. The name is at the end. Leaving the - between words is fine. How can I have the script pull those values out of that line and use them if its pasted in that format AND still do it the other way if it is in the other format.

I was thinking I could use an IF/THEN. If string begins with https, do this, else do that. That line would be put in column a so i think it would work. I just dont know how to pull those values and use them. I think the if/then would also let me mix the 2 formats together.

h t t p s:
**1drv. ms*x*s!ArYQSU2RyboygdpFyOPjyRTPHqco5w?e=A7xBvF

That is the latest version.

You'll have to put the link together. Take out the spaces. Put the periods. and replace the * with /. excel forum's wont let me post links yet.

[tester3x]

I guess I could just post the main script since that is where the new stuff would be.

Sub convert()
Dim lRow As Long, i, c, LR, x
Dim item As String
Dim num As String


name = Range("M10")
num = Range("N10")

bRow = Cells(Rows.Count, 1).End(xlUp).Row




lRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lRow

If IsEmpty(Range(name & i)) Then Range(name & i).Value = "Name unknown"

x = Application.WorksheetFunction.Substitute(Range(name & i), Chr(34), "") 'Substitute (True, “r”, “u”)

c = Application.WorksheetFunction.Substitute(x, " ", "") 'Substitute (True, “r”, “u”)

Range("K" & i) = "{[" & Chr(34) & "name" & Chr(34) & "] = " & Chr(34) & "Thanks LITTLEDUDE for SCOpener Scripter__" & c & Chr(34) & ",[" & Chr(34) & "id" & Chr(34) & "] = " & Range(num & i) & ",[" & Chr(34) & "button" & Chr(34) & "] = nil},"

Next i



End Sub

[tester3x]

Oh, and I had to add in taking out " cuz there were some names with that in and that throws off the whole script.

[k1dr0ck]

i think its best if you create a new thread with a sample file with a before and after data

[tester3x]

I just figured it out.