Converting many lists into one

[David Smith]

Hi all,

I'm new to this newsgroup and not sure if it's frowned upon to ask for a
snippet of code in this group - feel free to tell me off if this is the
case.

I have been part of a project at work to convert telephone services at
many (350) sites across Australia from one format to another. As part
of this our team has produced a list of phone numbers for each site. We
now have 350 separate CSV files (one list for each site), which we'd
like to convert into one big Excel file (one list for the whole project).

These 350 files are nothing but a list of numbers in column A. Some of
them have 10 or 12 rows of data, some have 2,500 or 3,000. File names
are based on the name of the site (eg Brisbane.csv, Sydney.csv) and are
not 'standardised' (ie they're not file1.csv, file2.csv, ..., file350.csv).

Would any of you gurus be kind enough to dash off some code which could
do the following?

<beginning of loop>
- open first CSV file
- go to the last cell in column A which contains data
- select all cells from that cell back up to cell A1
- copy and paste contents into workbook "national.xls" starting at the
first blank cell in that book
- close the CSV file
- open the next CSV file and repeat the process
<end of loop>

Thanks in advance,

David Smith

 

[JulieD]

Hi David

just need to check on the contents of the csv files ... you say that you
would like the code to go to the end of column A and select the whole lot &
paste into the first blank cell in an excel worksheet ... however, a csv
file doesn't have columns so i just want to check that you want the
entire contents of the csv files imported into Excel.

additionally by phone numbers - are they prefixed with a "0" ? if so
converting them to numbers in excel will lose this zero - do you want them
brought in as text or as numbers?

Cheers
JulieD

 

[David Smith]

Cheers Julie,

Answers to the questions are below...

just need to check on the contents of the csv files ... you say that you
would like the code to go to the end of column A and select the whole lot &
paste into the first blank cell in an excel worksheet ... however, a csv
file doesn't have columns so i just want to check that you want the
entire contents of the csv files imported into Excel.

Fair point, that's true of course and yes, I would be importing the CSV
files into Excel to work with them. I kind of assumed that without
making it explicit.

additionally by phone numbers - are they prefixed with a "0" ? if so
converting them to numbers in excel will lose this zero - do you want them
brought in as text or as numbers?

Keeping the 0 would be a bonus, but it's not vital - I figured if the
0's did not come across, I could add them back in when I had the
complete list, by simply putting 0 in column B for each of the 45,000 or
so rows, then using a formula like =B1&A1 all the way down in column C
to rebuild the records in the correct format. Would be nice not to have
to do that though! ;-)

 

[JulieD]

Hi David

sorry got the flu ... tend to take things a bit too literally

here's some code that should do what you want... haven't converted the
imported numbers into text but you could select column A of the spreadsheet
that you paste this code into (which is where the imported files will come
into) and make the cell format TEXT - this might keep the leading zero for
you (not tested)

Sub importcsv()

Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents\newsgroup examples\csv files" '(insert proper
file directory)
.SearchSubFolders = False
.Filename = "*.csv"
.MatchTextExactly = True
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i), Origin:=xlWindows,
Delimiter:=2
With ThisWorkbook.Sheets(1)
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
End With
ActiveWorkbook.Close
Range("A1").Select
If Len(ActiveCell) < 1 Then
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
End If
Next i
MsgBox "Finished"
Else
MsgBox "There were no files found"
End If
End With

Application.ScreenUpdating = True

End Sub


Hope this helps - let me know if you need further assistance.
PS please test on 10 or so files first ...

Cheers
JulieD

 

[David Smith]

Cheers Julie! It works brilliantly! FYI I had to add two lines to your
code (see below), because every time Excel went to close a CSV file, it
would throw up a warning message "you have placed a large amount of
information on the clipboard..." etc. All I had to do was turn the
warnings off at the start then back on at the end and it works beautifully.

I've been trying to work this out for myself for ages; I was using a
completely different approach, which may explain why I couldn't get it
to work!

Thanks for your assistance!

Regards,

David

Sub importcsv()

Application.ScreenUpdating = False

Application.DisplayAlerts = False

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents\newsgroup examples\csv files" '(insert proper
file directory)
.SearchSubFolders = False
.Filename = "*.csv"
.MatchTextExactly = True
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Workbooks.Open Filename:=.FoundFiles(i), Origin:=xlWindows,
Delimiter:=2
With ThisWorkbook.Sheets(1)
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
End With
ActiveWorkbook.Close
Range("A1").Select
If Len(ActiveCell) < 1 Then
ActiveSheet.Paste
Else
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
End If
Next i
MsgBox "Finished"
Else
MsgBox "There were no files found"
End If
End With

Application.ScreenUpdating = True

Application.DisplayAlerts = True

 

[JulieD]

Hi David

glad it works

Cheers
Julie

Cheers Julie! It works brilliantly! FYI I had to add two lines to your
code (see below), because every time Excel went to close a CSV file, it
would throw up a warning message "you have placed a large amount of
information on the clipboard..." etc. All I had to do was turn the
warnings off at the start then back on at the end and it works beautifully.

I've been trying to work this out for myself for ages; I was using a
completely different approach, which may explain why I couldn't get it
to work!

Thanks for your assistance!

Regards,

David

Application.DisplayAlerts = False
Application.DisplayAlerts = True