Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to determine if macro is possible
Hi Chris,
Ensure that you have a backup of your workbook before running the following macro in case it does not do exactly what you expect. Note the comments in the code where you might have to edit the code to replace the master worksheet name, path of the csv file and csv file name. If the csv file and master file are in the same folder then there is no need to replace the csv file path. Some people would use the last row of the used range to find the last row of data on the master worksheet. Not terribly reliable under some circumstances so I have used 'find the last cell with data'. Copying the used range from the csv file is not a problem under the circumstances that I have used it. Sub AppendData() Dim wbMaster As Workbook Dim wsMaster As Worksheet Dim lngLastRow As Long Dim strPath As String Dim strTxtFile As String 'Can edit 'ThisWorkbook.Path' in next line to 'actual text file path between double quotes strPath = ThisWorkbook.Path & "\" 'Edit "Import Test.csv" to your csv file name strTxtFile = "Import Test.csv" Set wbMaster = ThisWorkbook 'Edit "Sheet1" in next line to your 'Master worksheet name Set wsMaster = wbMaster.Sheets("Sheet1") 'Find last row of existing data in master sheet With wsMaster lngLastRow = .Cells.Find(What:="*", _ After:=.Cells(.Rows.Count, .Columns.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row + 1 End With 'Open csv file Workbooks.Open Filename:=strPath & strTxtFile 'Copy the data from csv file and append to 'bottom of existing data in Master worksheet 'Note: the + 1 leaves one blank row between 'existing data and new appended data. 'Delete + 1 for no blank rows. ActiveWorkbook.ActiveSheet.UsedRange.Copy _ wsMaster.Cells(lngLastRow + 1, "A") 'Close the csv file Windows(strTxtFile).Close End Sub -- Regards, OssieMac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to determine if macro is possible
Hi again Chris,
Meant to also say there are other ways of finding the last row of data if it is known that a particular column will always have data in every row. However, your original post did not stipulate this. The method that I used should always find the last row irrespective of whether any columns have data in every row. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to determine if macro is possible
OK, found your first reply...
You asked if my input (guessing input, not master) file would always have data in a given column - unfortunately no. Basically, there's a title row, blank row, another title, another blank row, etc., then data. Now, once you hit that data, it's solid data until the bottom. I just got rid of the title junk (seems to be a consistent 7 rows) and just worked with the data. Sorry for asking such a basic question, but looking at your macro(?), how do I enter this? Do I just somehow add it in the VB window? And what do I need to update / change to make it work? You have: 'Can edit 'ThisWorkbook.Path' in next line to 'actual text file path between double quotes strPath = ThisWorkbook.Path & "\" So I just update it to point to where the file is located, such as: strPath = ThisWorkbook.Path & "C:\Temp\" thanks, Chris "OssieMac" wrote: Hi Chris, Ensure that you have a backup of your workbook before running the following macro in case it does not do exactly what you expect. Note the comments in the code where you might have to edit the code to replace the master worksheet name, path of the csv file and csv file name. If the csv file and master file are in the same folder then there is no need to replace the csv file path. Some people would use the last row of the used range to find the last row of data on the master worksheet. Not terribly reliable under some circumstances so I have used 'find the last cell with data'. Copying the used range from the csv file is not a problem under the circumstances that I have used it. Sub AppendData() Dim wbMaster As Workbook Dim wsMaster As Worksheet Dim lngLastRow As Long Dim strPath As String Dim strTxtFile As String 'Can edit 'ThisWorkbook.Path' in next line to 'actual text file path between double quotes strPath = ThisWorkbook.Path & "\" 'Edit "Import Test.csv" to your csv file name strTxtFile = "Import Test.csv" Set wbMaster = ThisWorkbook 'Edit "Sheet1" in next line to your 'Master worksheet name Set wsMaster = wbMaster.Sheets("Sheet1") 'Find last row of existing data in master sheet With wsMaster lngLastRow = .Cells.Find(What:="*", _ After:=.Cells(.Rows.Count, .Columns.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row + 1 End With 'Open csv file Workbooks.Open Filename:=strPath & strTxtFile 'Copy the data from csv file and append to 'bottom of existing data in Master worksheet 'Note: the + 1 leaves one blank row between 'existing data and new appended data. 'Delete + 1 for no blank rows. ActiveWorkbook.ActiveSheet.UsedRange.Copy _ wsMaster.Cells(lngLastRow + 1, "A") 'Close the csv file Windows(strTxtFile).Close End Sub -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to determine if macro is possible
Hi Chris,
Been away for a couple of days so late getting back to you. Have amended my previous code to disregard the first 7 lines. Note the comments in the code because you can adjust the number of lines at the top to ignore by changing just one line of code with the number. Also note the comments on how to insert the actual path in lieu of ThisWorkbook.Path Sub AppendData() Dim wbMaster As Workbook Dim wsMaster As Worksheet Dim lngLastRow As Long Dim strPath As String Dim strTxtFile As String Dim rngToCopy As Range Dim lngRows As Long 'Edit 7 in the following line to number 'of rows at top of text file to ignore lngRows = 7 strPath = ThisWorkbook.Path & "\" 'Can use following method in lieu of previous 'line of code. (Delete above line and remove 'the single quote (comment symbol) in next line 'strPath = "C:\Temp\" 'Edit "Import Test.csv" to your csv file name strTxtFile = "Import Test.csv" Set wbMaster = ThisWorkbook 'Edit "Sheet1" in next line to your 'Master worksheet name Set wsMaster = wbMaster.Sheets("Sheet1") 'Find last row of existing data in master sheet 'find first blank row below existing data) With wsMaster lngLastRow = .Cells.Find(What:="*", _ After:=.Cells(.Rows.Count, .Columns.Count), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row + 1 End With 'Open csv file Workbooks.Open Filename:=strPath & strTxtFile 'Set the range to copy from the .csv file. '.Offset(lngRows, 0) moves down to start copy range at row 8. 'However, it now includes additional 7 blank rows at bottom. '.Resize(.Rows.Count - lngRows, .Columns.Count) reduces 'the size by 7 rows to remove additional blank rows at bottom. With ActiveSheet.UsedRange Set rngToCopy = .Offset(lngRows, 0) _ .Resize(.Rows.Count - lngRows, .Columns.Count) _ .EntireRow End With 'Copy the data from csv file and append to 'bottom of existing data in Master worksheet 'Note: the + 1 leaves one blank row between 'existing data and new appended data. 'Delete + 1 for no blank rows between existing 'data and new data. rngToCopy.Copy _ wsMaster.Cells(lngLastRow + 1, "A") 'Close the csv file Windows(strTxtFile).Close End Sub -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to determine chart name | Excel Programming | |||
Macro to determine values | Excel Programming | |||
how can you let macro determine to where to copy | Excel Discussion (Misc queries) | |||
Use Combo Box value to determine macro used | Excel Worksheet Functions | |||
Varibles determine macro to run | Excel Programming |