Excel VBA - copy csv info to specific cells

[Vince Bowman]

So I am fairly new to Visual Basic, but am fluent in C++ and Perl
(just so you know some of my background to help me haha)

I am am currently trying to write a VB script in excel to fill out
approx.700 cover sheets that have been provided for me (i.e. I can't
change the location of the cells, or add any more).

As of right now, I have about 20 clients that need this done for them.
Each client has a csv file with the appropriate data. However, each
files contains multiple permits, and each permit needs a coversheet
( as in for each client file, anywhere from 5 to 100 coversheets must
be made).

As previously stated, I have a previously laid out coversheet (that
should remain the same format for each permit, just different
information) in which all the data must be in specific cells.


Not sure how to get start on this(VB code wise).

I could divide each client file by permit number, and have a csv file
for each coversheet (permit number). I could create a master template
out of the provided coversheet and point each individual cell to the
csv file, but I would have to do that for some 700 coversheets. Since
this is only a one time thing, I don't see much benefit in doing it
the way (as in once the cover sheets are created, there should be only
minimal changes that need to be made from time to time, and a script
would take more time than what its worth.)

I could also write a script in VB in which a new file is made from the
template (provided coversheet) for each csv file. Can I do this with a
VB script in Excel? Not sure how you would get started on this.

Would it be more beneficial to write some type of overarching program
in C++ (or more likely Perl) to pass each file into the template (in
which I write some simple script to point the data to the cells I
want).



It seems to me like the third would be the most time efficient, but
not sure how to pass a file to a VB script within an excel file.


I know I made this probably more wordy than I should, but I've helped
people on forums before, and know how inconveniencing it can be to
have unclear questions. My question may still be unclear, but
hopefully not. haha.



Thanks

 

[Vince Bowman]

So I am fairly new to Visual Basic, but am fluent in C++ and Perl
(just so you know some of my background to help me haha)

I am am currently trying to write a VB script in excel to fill out
approx.700 cover sheets that have been provided for me (i.e. I can't
change the location of the cells, or add any more).

As of right now, I have about 20 clients that need this done for them.
Each client has a csv file with the appropriate data. However, each
files contains multiple permits, and each permit needs a coversheet
( as in for each client file, anywhere from 5 to 100 coversheets must
be made).

As previously stated, I have a previously laid out coversheet (that
should remain the same format for each permit, just different
information) in which all the data must be in specific cells.

Not sure how to get start on this(VB code wise).

I could divide each client file by permit number, and have a csv file
for each coversheet (permit number). I could create a master template
out of the provided coversheet and point each individual cell to the
csv file, but I would have to do that for some 700 coversheets. Since
this is only a one time thing, I don't see much benefit in doing it
the way (as in once the cover sheets are created, there should be only
minimal changes that need to be made from time to time, and a script
would take more time than what its worth.)

I could also write a script in VB in which a new file is made from the
template (provided coversheet) for each csv file. Can I do this with a
VB script in Excel? Not sure how you would get started on this.

Would it be more beneficial to write some type of overarching program
in C++ (or more likely Perl) to pass each file into the template (in
which I write some simple script to point the data to the cells I
want).

It seems to me like the third would be the most time efficient, but
not sure how to pass a file to a VB script within an excel file.

I know I made this probably more wordy than I should, but I've helped
people on forums before, and know how inconveniencing it can be to
have unclear questions. My question may still be unclear, but
hopefully not. haha.

Thanks

When I say third, I'm referring to writing a C++ pr Perl program to
pass the file name and/or data to the excel script.

Thanks again

 

[Tim Williams]

This will create the cover sheets as new worksheets in a workbook (one
workbook per input csv)

It will process all of the csv files it finds in the input folder.

Tim


Sub Tester()

Const FPATH As String = "C:\local files\CSVData\"

Dim fCSV As String
Dim wb As Excel.Workbook, wb2 As Excel.Workbook
Dim sht As Excel.Worksheet
Dim templt As Excel.Worksheet
Dim i As Integer

Set templt = ThisWorkbook.Sheets("Template")

fCSV = Dir(FPATH & "*.csv")
Do While fCSV <> ""

Set wb = Workbooks.Open(FPATH & fCSV)
Set wb2 = Workbooks.Add()
wb2.SaveAs FPATH & "covers_" & _
Replace(wb.Name, ".csv", ".xls")

Set sht = wb.Sheets(1)
i = 1
Do While sht.Cells(i, 1) <> ""
With templt
.Range("A2").Value = sht.Cells(i, 1).Value
.Range("B10").Value = sht.Cells(i, 2).Value
'transfer rest of values
End With

templt.Copy After:=wb2.Sheets(wb2.Sheets.Count)
wb2.Sheets(wb2.Sheets.Count).Name = "Cover " & i
i = i + 1
Loop

wb.Close False
wb2.Close True

fCSV = Dir()
Loop

End Sub

 

[Vince Bowman]

Thanks a lot!

I can follow your code and it seems to work fine, except when add your
statement

Set templt =
ThisWorkbook.Sheets("Template")


It keeps erroring out that my subscript is out of range. I know that
the statement requires the template as the object, but how should I
define it?




Regard,

VB

 

[Tim Williams]

Addressed off-line. Macro needed to be in a general module and not in
a worksheet code module.

Tim