Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - copy csv info to specific cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - copy csv info to specific cells
On Dec 21, 12:41*pm, Vince Bowman wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - copy csv info to specific cells
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 On Dec 21, 9:46*am, Vince Bowman wrote: On Dec 21, 12:41*pm, Vince Bowman wrote: 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- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - copy csv info to specific cells
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 On Dec 21, 8:23*pm, Tim Williams wrote: 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 On Dec 21, 9:46*am, Vince Bowman wrote: On Dec 21, 12:41*pm, Vince Bowman wrote: 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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - copy csv info to specific cells
Addressed off-line. Macro needed to be in a general module and not in a worksheet code module. Tim On Dec 22, 11:36*am, Vince Bowman wrote: 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 On Dec 21, 8:23*pm, Tim Williams wrote: 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 On Dec 21, 9:46*am, Vince Bowman wrote: On Dec 21, 12:41*pm, Vince Bowman wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy info between 2 specific words | Excel Programming | |||
Copy info into empty cells below info, until finds cell with new d | Excel Discussion (Misc queries) | |||
How can I make Excel copy and paste specific cells every 15minuts | Excel Programming | |||
extract non-specific info from multiple cells | Excel Discussion (Misc queries) | |||
extract specific info from cells in a column | New Users to Excel |