Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Data from 2 Workbooks in the same folder
Hi All,
I would appreciate some assistance with the following: 1. I have three excel workbooks in a folder. The name of the first workbook starts with the letter "P". The second workbook's name starts with "S" and the third is called Daily Recon. 2. Workbooks P and S represent data dumps from two different systems. The Daily Recon consolidates the data dumpts from the P and S workbooks. 3. Each day these three files are saved in a new folder named after the day the data dumps correspond to. For example Mar 1, Mar 2 etc. 4. I would like to add a button in the Daily Recon workbook called "Import" which will import the data on the first worksheet of the workbook named "P" starting at row 10 to column J and down for as many rows as there is data into a worksheet in the Daily Recon workbook called "P". At the same time the code should also import the data from the "S" workbook from the first worksheet but starting at row 1 to column m and down for as many rows as there is data into a worksheet named "S" in the Daily Recon workbook. 5. The Daily Recon workbook will already have two worksheets called "P" and "S" so there is no need to insert new sheets during the import processes. 6. I am using Excel 2002 on windows XP. Any ideas on how to accomplish the above would be greatly appreciated. Thanks, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import Data from 2 Workbooks in the same folder
I don't know your expertise in VBA or Excel. This is a starting point and I
will assit as needed by replying to my message. The code below is a good starting point for doing your copying. It give an example of finding the last row with data and how to copy from one file to another. Get this code working then add a button. The button gets added by going to the View Menu - Toolbar - Control toolbox. Add a Command button. The Command button can be selected to run any macro. Sub getworkbook() Set OldWorkbook = ThisWorkbook OldLastRow = OldWorkbook.Worksheets("sheet1"). _ Cells(1, 1).End(xlDown).Row MyFileName = "c:\temp\abc.xls" Workbooks.Open Filename:=MyFileName, ReadOnly:=True ' Removed pathname from file name so it can be referenced in this program. 'Basic doesn't like the full pathname???? stupid microsoft Do While (1) CharPosition = InStr(MyFileName, "\") If CharPosition 0 Then MyFileName = Mid(MyFileName, CharPosition + 1) Else Exit Do End If Loop Lastrow = Workbooks(MyFileName). _ Worksheets("sheet1").Cells(10, 1).End(xlDown).Row Set MyRange = Workbooks(MyFileName).Worksheets("sheet1"). _ Range(Cells(10, 1), Cells(Lastrow, 1)).EntireRow MyRange.Copy MyRange.Copy Destination:=OldWorkbook.Worksheets("Sheet1"). _ Cells(OldLastRow + 1, 1) Workbooks(MyFileName).Close SaveChanges:=False End Sub "steve" wrote: Hi All, I would appreciate some assistance with the following: 1. I have three excel workbooks in a folder. The name of the first workbook starts with the letter "P". The second workbook's name starts with "S" and the third is called Daily Recon. 2. Workbooks P and S represent data dumps from two different systems. The Daily Recon consolidates the data dumpts from the P and S workbooks. 3. Each day these three files are saved in a new folder named after the day the data dumps correspond to. For example Mar 1, Mar 2 etc. 4. I would like to add a button in the Daily Recon workbook called "Import" which will import the data on the first worksheet of the workbook named "P" starting at row 10 to column J and down for as many rows as there is data into a worksheet in the Daily Recon workbook called "P". At the same time the code should also import the data from the "S" workbook from the first worksheet but starting at row 1 to column m and down for as many rows as there is data into a worksheet named "S" in the Daily Recon workbook. 5. The Daily Recon workbook will already have two worksheets called "P" and "S" so there is no need to insert new sheets during the import processes. 6. I am using Excel 2002 on windows XP. Any ideas on how to accomplish the above would be greatly appreciated. Thanks, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open many *.tsv files in folder and import the data into Excel | Excel Programming | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming | |||
How do I change the default folder when I import data? | Excel Worksheet Functions | |||
exporting data from Workbooks in Folder | Excel Programming |