Macro in Excel to get data??

[chrisaroundtown]

Hi,

When I open an excel document I want it to go into a file (let's say C:/Tempdatadump.txt), then copy and paste this data into a sheet in my excel document.

Does anyone know how to do this?

Thanks
Chris

 

[LizF]

Hi Chris,

How's your VBA?! This is certainly do-able with VBA but I don't know if you can do it just with the macro recorder.

A couple of questions - how many columns of data are there in the text file, and will there always be the same number of columns?

Assuming you don't have many columns, you probably need something along the lines of the following... (if there's more than 3 or 4 columns, or if they change, it'd be more efficient to put an extra loop in)

Sub FileTest()

Dim Column_In_Text_File_1 As String
Dim Column_In_Text_File_2 As String
ActiveWorkbook.Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
' will paste the data in starting at cell A1 in Sheet1

Open "C:/Tempdatadump.txt" For Input As #1
Do While Not EOF(1)
    Input #1, Column_In_Text_File_1, Column_In_Text_File_2
   'Debug.Print Column_In_Text_File_1, Column_In_Text_File_2
    ActiveCell.Value = Column_In_Text_File_1
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = Column_In_Text_File_2
    ActiveCell.Offset(1, -1).Select
    Loop
Close #1
End sub
[\code]

Let me know if that's unclear - I'm afraid it's kinda "clunky" code but I can neaten things up if you need more help

HTH
Liz

 

[SkipVought]

Hi, chrismerry,

What does you data look like? It could be that you could use the text import wizard IF...

1. your data is columnar
2. the columns are delimited OR
3. the columns are fixed width

The import wizard makes it really easy! Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[chrisaroundtown]

Thanks,

I am getting the error "File already open" at Open "C:/Tempdatadump.txt". I'll have to look into that.

Should 'Debug.Print Column_In_Text_File_1, Column_In_Text_File_2
be a comment?

There will always be 8 columns, they are Split,Date,Calls Offered,ACD Calls,Answered Within Service Level,% Ans Rate Within Service Level,Abn Calls After Service Level and % Abn Rate After Service Level. The code doesn't like Date so I canchange it to DayDate.

Here is my code:

Dim Split As String
Dim DayDate As String
ActiveWorkbook.Sheets("RS&quot.Activate
ActiveSheet.Range("A1&quot.Select
' will paste the data in starting at cell A1 in Sheet1

Close "C:/Tempdatadump.txt"
Open "C:/Tempdatadump.txt" For Input As #1
Do While Not EOF(1)
Input #1, Split, DayDate
'Debug.Print Column_In_Text_File_1, Column_In_Text_File_2
ActiveCell.Value = Split
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = DayDate
ActiveCell.Offset(1, -1).Select
Loop
Close #1

 

[chrisaroundtown]

Ok ok, I got it working.

But wait...hold your applause.

Is there anyway to make it faster? It just seams to be going one cell at a time.

Also, is there anyway to make the code shorter?

Here is my code:
Private Sub Worksheet_Activate()
Dim DateDay As String
Dim Time As String
Dim Time1 As String
Dim Time30 As String
Dim Split As String
Dim CallsOffered As String
Dim ACDCalls As String
Dim ASA As String
Dim AverageAbnTime As String
Dim AbanCalls As String
Dim AbanCallsASL As String
Dim WithinSL As String
Dim AbnRate As String

ActiveWorkbook.Sheets("RS&quot.Activate
ActiveSheet.Range("K1&quot.Select
Open "C:/CBA Master Int Dump CSV.csv" For Input As #1
Do While Not EOF(1)
Input #1, DateDay, Time, Time1, Time30, Split, CallsOffered, ACDCalls, ASA, AverageAbnTime, AbanCalls, AbanCallsASL, WithinSL, AbnRate

Debug.Print Time

ActiveCell.Value = DateDay
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time1
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time30
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Split
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = CallsOffered
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ACDCalls
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ASA
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = AverageAbnTime
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = AbanCalls
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = AbanCallsASL
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = WithinSL
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = AbnRate
ActiveCell.Offset(1, -12).Select


Loop
End Sub

Thanks
Chris

 

[IRstuff]

Is the data comma-separated, otherwise delineated or fixed format?

It might be easier to drag and drop the file into Excel, then have a macro that does the TextToColumn conversion with the right formatting and then copy the data to the desired place.

I do that right now with data files with anywhere from about 1,000 to 30,000 rows of data with around 100 columns of data. It's partially manual right now, since I'm using existing files with existing formulas, but it seems like it should be fully or at least more automateable. I just ran a file that was 23Mb, 27968 rows, 130 columns; after it was loaded into Excel, the VBA routine to to the conversion took 12.6 sec.

 

[LizF]

Hi Chris,

Yeah, the debug.print was supposed to be commented out - that was just in there to check I was picking up the right data (before I added the writing to cells bit)

One way to speed things up might be to go through the text file storing data in an array, then just stick the whole array into the excel sheet. This should be faster because you're not selecting cells, writing to cells, moving cells the whole time

Does anyone know if you can get VBA to count the columns in a text file? If so you could put a loop in and avoid having to define each column separately


However, irstuff/skip's suggestions sound like they would be less hassle...!

HTH
Liz

 

[SkipVought]

Give it a try! Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com