Acen
10-29-2012, 07:10 PM
Hello.
First things first, VBA is the hardest language to learn I've ever had the pleasure? of working with.
Straight, easy to follow documentation seems to be impossible to find.
Anyway --
What I'm trying to do:
Copy data from one spreadsheet (or worksheet) to another two spreadsheets (/worksheets) and rearrange the columns differently on both exported spreadsheets. Then, filling in certain columns on the outgoing spreadsheets with specified data.
I've never used VBA before, however it's pretty necessary for me to learn - so here I go.
This is what I have.
It rearranges the data as per contactimport.csv and enroleimport.csv
Sub Macro1()
'
' Macro1 Macro
'
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Dim objWorkbook As Workbook
Set objWorkbook = objExcel.Workbooks.Open("C:\input.xlsx")
objWorkbook.Sheets(1).Activate
objWorkbook.Worksheets.Add
objWorkbook.Sheets(2).Activate
Dim F As Long, fromRange As String, toRange As String
F = FreeFile
Open "C:\contactimport.csv" For Input As F
While Not EOF(F)
Input #F, fromRange, toRange
Set objWorksheet = objWorkbook.Worksheets(2)
objWorksheet.Activate
objWorkbook.Sheets(2).Range(fromRange).EntireColumn.Copy
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate
Set objRange = objWorkbook.Sheets(1).Range(toRange)
objWorksheet.Paste (objRange)
Wend
Close F
objWorkbook.Worksheets.Add
Open "C:\enroleimport.csv" For Input As F
While Not EOF(F)
Input #F, fromRange, toRange
Set objWorksheet = objWorkbook.Worksheets(3)
objWorksheet.Activate
objWorkbook.Sheets(3).Range(fromRange).EntireColumn.Copy
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate
Set objRange = objWorkbook.Sheets(1).Range(toRange)
objWorksheet.Paste (objRange)
Wend
Close F
End Sub
input.xlsx
See attachment. Filled with a row of test data.
contactimport.csv
B1,E1
C1,D1
D1,F1
E1,Q1
F1,AO1
G1,AP1
H1,AR1
I1,AS1
J1,AT1
K1,AU1
L1,AW1
M1,AX1
N1,AZ1
O1,BA1
P1,BB1
Q1,BC1
R1,BD1
S1,BE1
T1,BO1
W1,M1
X1,V1
AE1,T1
AG1,S1
AH1,G1
AI1,AA1
AJ1,J1
enroleimport.csv
AB1,L1
AC1,M1
AL1,R1
AM1,S1
AN1,T1
AF1,X1
Z1,AH1
So now, what I need it to do, is add another one or two simple CSV files with the same sort of layout (for simplicity later on).
Say like
A2,cluster
D2,banana
Would have column A2, below A1 to leave the name, filled with "cluster" down every row.
The same with D2. Every row in D apart from 1, filled with the value "banana".
So, help please?
Also any suggestions on what I have so far would be much appreciated.
Notepad for progress:
Dim Populate As Long, columnChoice As String, columnData As String
Populate = FreeFile
Open "C:\populatedatacontact.csv" For Input As Populate
While Not EOF(Populate)
Input #Populate, columnChoice, columnData
Set objWorksheet = objWorkbook.Worksheets(2)
objWorksheet.Activate
Wend
Close Populate
First things first, VBA is the hardest language to learn I've ever had the pleasure? of working with.
Straight, easy to follow documentation seems to be impossible to find.
Anyway --
What I'm trying to do:
Copy data from one spreadsheet (or worksheet) to another two spreadsheets (/worksheets) and rearrange the columns differently on both exported spreadsheets. Then, filling in certain columns on the outgoing spreadsheets with specified data.
I've never used VBA before, however it's pretty necessary for me to learn - so here I go.
This is what I have.
It rearranges the data as per contactimport.csv and enroleimport.csv
Sub Macro1()
'
' Macro1 Macro
'
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Dim objWorkbook As Workbook
Set objWorkbook = objExcel.Workbooks.Open("C:\input.xlsx")
objWorkbook.Sheets(1).Activate
objWorkbook.Worksheets.Add
objWorkbook.Sheets(2).Activate
Dim F As Long, fromRange As String, toRange As String
F = FreeFile
Open "C:\contactimport.csv" For Input As F
While Not EOF(F)
Input #F, fromRange, toRange
Set objWorksheet = objWorkbook.Worksheets(2)
objWorksheet.Activate
objWorkbook.Sheets(2).Range(fromRange).EntireColumn.Copy
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate
Set objRange = objWorkbook.Sheets(1).Range(toRange)
objWorksheet.Paste (objRange)
Wend
Close F
objWorkbook.Worksheets.Add
Open "C:\enroleimport.csv" For Input As F
While Not EOF(F)
Input #F, fromRange, toRange
Set objWorksheet = objWorkbook.Worksheets(3)
objWorksheet.Activate
objWorkbook.Sheets(3).Range(fromRange).EntireColumn.Copy
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate
Set objRange = objWorkbook.Sheets(1).Range(toRange)
objWorksheet.Paste (objRange)
Wend
Close F
End Sub
input.xlsx
See attachment. Filled with a row of test data.
contactimport.csv
B1,E1
C1,D1
D1,F1
E1,Q1
F1,AO1
G1,AP1
H1,AR1
I1,AS1
J1,AT1
K1,AU1
L1,AW1
M1,AX1
N1,AZ1
O1,BA1
P1,BB1
Q1,BC1
R1,BD1
S1,BE1
T1,BO1
W1,M1
X1,V1
AE1,T1
AG1,S1
AH1,G1
AI1,AA1
AJ1,J1
enroleimport.csv
AB1,L1
AC1,M1
AL1,R1
AM1,S1
AN1,T1
AF1,X1
Z1,AH1
So now, what I need it to do, is add another one or two simple CSV files with the same sort of layout (for simplicity later on).
Say like
A2,cluster
D2,banana
Would have column A2, below A1 to leave the name, filled with "cluster" down every row.
The same with D2. Every row in D apart from 1, filled with the value "banana".
So, help please?
Also any suggestions on what I have so far would be much appreciated.
Notepad for progress:
Dim Populate As Long, columnChoice As String, columnData As String
Populate = FreeFile
Open "C:\populatedatacontact.csv" For Input As Populate
While Not EOF(Populate)
Input #Populate, columnChoice, columnData
Set objWorksheet = objWorkbook.Worksheets(2)
objWorksheet.Activate
Wend
Close Populate