Excel Help

[ChocolateBear]

Hi Guys

Some help from the Excel bofs will be appreciated here...

Here's the Beef:

• I basically have a folder containing about 30 csv files
• I need these merged into a single workbook, with separate worksheets that will contain the data from the csv file. (i.e. I want a single workbook with 30 worksheets)

Can anybody help? Any ideas how to do this?

Thanks

 

[Datura]

Dunno if there is an easier way, create a book and you will just have to right click each sheet 1 at a time from each book and select move to the book you have created

 

[ChocolateBear]

Yeah I was hoping to avoid that

I wouldn't mind doing that if it was just the one set of 30 csv's, but its a whole lot of them (like 6 or so). plus this isn't a once of procedure so I was hoping someone had some VBA code lying around

Thanks anyway

 

[Datura]

Yeah I was hoping to avoid that

I wouldn't mind doing that if it was just the one set of 30 csv's, but its a whole lot of them (like 6 or so). plus this isn't a once of procedure so I was hoping someone had some VBA code lying around

Thanks anyway

Hope someone does coz I also get stuck with this sometimes

 

[Luke7777]

Maybe you can look at this ?

 

[Datura]

Found this:

Answer -
use SAVEAS instead of Move.

use one of the options given with slight changes in coding to suit your needs.
Private Sub Command1_Click()
Dim pip As String, xl As excel.Application
Set xl = CreateObject("excel.Application")

xl.Workbooks.Open ("C:\my documents\fresh.xls")
xl.Worksheets("sheet1").Range("A10").Value = "Anydata2"
pip = "Gadbad"
xl.SaveWorkspace ("C:\my documents\" & pip & " .xls")
xl.Application.Quit
Set xl = Nothing
Print "over"
End Sub
OPTION 2
Private Sub Command1_Click()

Dim pip As String , xl As Excel.Workbook
Set xl = GetObject("c:\my documents\fresh.xls")

xl.Worksheets("sheet1").Range("A10").Value = "Anydata2"

pip = xl.Worksheets("sheet1").Range("A1").Value ' here the name to save is entered
' or you can use pip ="magician"
xl.SaveAs ("c:\my documents\" & pip & " .xls")

xl.Application.Quit
Set xl = Nothing
Print "over"
End Sub

Here

 

[Nod]

There is a perl module called "Spreadsheet::WriteExcel", which might help.

To write a string, a formatted string, a number and a formula to
the first worksheet in an Excel workbook called perl.xls:

use Spreadsheet::WriteExcel;

# Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("perl.xls");

# Add a worksheet
$worksheet = $workbook->add_worksheet();

# Add and define a format
$format = $workbook->add_format(); # Add a format
$format->set_bold();
$format->set_color('red');
$format->set_align('center');

# Write a formatted and unformatted string.
$col = $row = 0;
$worksheet->write($row, $col, "Hi Excel!", $format);
$worksheet->write(1, $col, "Hi Excel!");

# Write a number and a formula using A1 notation
$worksheet->write('A3', 1.2345);
$worksheet->write('A4', '=SIN(PI()/4)');

Write a script to read in every csv file, and then write them out to their own worksheet.

 

[ChocolateBear]

Cool thanks guys

I'll test them all out out and let you know how it goes!

 

[Vember]

I had a look and im quite sure it is simple, if not sorry.

>> File open
>> in the area "Files of type" drop the arrow and put in either "Text files" or "all files"
>> select the file and open, it gives you a "text import wizard" select what you want and I think you set.

No hassle no fuss... now where does that come from

 

[Luke7777]

I had a look and im quite sure it is simple, if not sorry.

>> File open
>> in the area "Files of type" drop the arrow and put in either "Text files" or "all files"
>> select the file and open, it gives you a "text import wizard" select what you want and I think you set.

No hassle no fuss... now where does that come from

Apology accepted

 

[Vember]

Apology accepted

ok wait really? .. i was talking about the "comma delimited" not the Macintosh or the MS dos one .

just checking but ok.

 

[Luke7777]

The problem is that ChocolateBear is lazy and doesn't want to open the files seperately

 

[Nod]

The problem is that ChocolateBear is lazy and doesn't want to open the files seperately

Using the perl thing might be more work in the beginning, but if he do it right, he will only have to do once. In theory
That is in essence the mark of a truly lazy admin. Write scripts for everything, but only once.

 

[ChocolateBear]

Ok Ok

so this is what i came up with using some of my own code and code from the net

Function GetFileList(FileSpec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False

Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String

On Error GoTo NoFilesFound

FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound

' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function

' Error handler
NoFilesFound:

GetFileList = False

End Function


Sub test()
Dim p As String, x As Variant
Dim wsname As String


p = "directory where .csv files are located"
x = GetFileList(p)

Select Case IsArray(x)
Case True 'files found
MsgBox UBound(x)
For i = LBound(x) To UBound(x)
Workbooks.Open FileName:= p & x(i)
wsname = Replace(x(i), ".csv", "")

Workbooks(wsname & ".csv").Worksheets(wsname).Move After:=Workbooks("Book1").Sheets(1)

Next i
Case False 'no files found
MsgBox "No matching files"
End Select
End Sub

Edit: Fixed it!! apparently the directory you assign has to look like this p = "C:\My Documents\" . It doesnt like it if u leave the trailing backslash out!

 

[ChocolateBear]

Oh and u can change the destination workbook to whatever u want I suppose... I just left it at "Book1" cos i'm too lazy, besides you have to go save the file afterwards anyway


some feedback testing would be cool...though it seems to be working on my side... but its past 5pm and I may be imagining it!

 

[ChocolateBear]

The problem is that ChocolateBear is lazy and doesn't want to open the files seperately

Hahaha...well laziness pay off Just saved myself tons of manual, mind-numbing right-clicking!!!

At least if you try to automate it, you have to think about what you're doing!!