Change the type of data

Question

I want to blend the data from two files into one single excel sheet the visual basic code must import the two sheets into one.

What I want to do is changing the data types of that different data in order for them to fit in the same column.

For example : FILE A

col A | col B
int(2)|int(4)

FILE B

col A | col B
int(5)| int(2)

must blend into

File C

col A | col B
int(3)| int(3)

how to do this kind of script? No idea where to start. The data type could be anything not just int, i just want them to be defined somewhere in the code.

edit :

The content from file A and file B must appear in file C The column A from file A and B must go to file C in a column also called A with all the data from fileA:A and fileB:A

As Tom mentioned, can you give more detail as to how you want the columns blended (aka merged I assume)? For example is File C's colA = FileAcolA & FileBcolB or sometimes col A from file a and sometimes the one from file b based on content? — Bryan, Sep 10 '15 at 17:55
I just edited my message, can you take the minus back if it's ok now? — Jules, Sep 11 '15 at 15:09
As Bryan said - how does **int(2)** and **int(5)** get 'blended' to equal **int(3)**? If all the data from file A & B must appear in the same column in file C surely you'd expect it to be **int(2)int(5)**? — Darren Bartrup-Cook, Sep 11 '15 at 15:49

score 0 · Answer 1 · edited May 23 '17 at 12:14

Still not sure what you want but here is some code and a link to get you started. The code I will be using is a trimmed down version that can be found here or here. The code below gets data from the xlsx files without opening them and combines the two sets of column data with a comma.

Dim FileAColA As Variant
Dim FileAColB As Variant
Dim FileBColA As Variant
Dim FileBColB As Variant
Dim arg As String
Dim i As Integer

i = 2

'Gets data from FileA col a then b then same for fileB
arg = "'C:\Workspace\trash\[FileA.xlsx]Sheet1'!R" & i & "C1"
FileAColA = ExecuteExcel4Macro(arg)
arg = "'C:\Workspace\trash\[FileA.xlsx]Sheet1'!R" & i & "C2"
FileAColB = ExecuteExcel4Macro(arg)
arg = "'C:\Workspace\trash\[FileB.xlsx]Sheet1'!R" & i & "C1"
FileBColA = ExecuteExcel4Macro(arg)
arg = "'C:\Workspace\trash\[FileB.xlsx]Sheet1'!R" & i & "C2"
FileBColB = ExecuteExcel4Macro(arg)

'While loop to continue until no data in all 4 cells, when no data in cell varient will be 0
While FileAColA <> 0 And FileAColB <> 0 And FileBColA <> 0 And FileBColB <> 0
    Cells(i, 1).Value = FileAColA & "," & FileBColA 'Combines values with a comma in between
    Cells(i, 2).Value = FileAColB & "," & FileBColB 'Combines values with a comma in between

    i = i + 1

    'Gets data from FileA col a then b then same for fileB
    arg = "'C:\Workspace\trash\[FileA.xlsx]Sheet1'!R" & i & "C1"
    FileAColA = ExecuteExcel4Macro(arg)
    arg = "'C:\Workspace\trash\[FileA.xlsx]Sheet1'!R" & i & "C2"
    FileAColB = ExecuteExcel4Macro(arg)
    arg = "'C:\Workspace\trash\[FileB.xlsx]Sheet1'!R" & i & "C1"
    FileBColA = ExecuteExcel4Macro(arg)
    arg = "'C:\Workspace\trash\[FileB.xlsx]Sheet1'!R" & i & "C2"
    FileBColB = ExecuteExcel4Macro(arg)
Wend

If you don't want them to combine with a comma and instead want to add them together then instead of FileAColA & "," & FileBColA you can do FileAColA + FileBColA. If you want them combined in some other way, you will have to explain in more detail and preferably include examples of before and after column data.

Change the type of data

1 Answers1