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

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

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

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