SOLVED reading from a csv document

[LodBot]

How can I read data from a .csv document into a .xls document? Something along the lines of this:

Range("A1").Value = Doc("blah.csv").Sheets("blah").Range("A1").Value

I made up the Doc function, but I think you guys know what I mean

Thanks,
Alex

[opus]

One way is to open the .csv file using EXCEL.
If you are using VBA try something like this:
I assume your .xls is open (holding this code in a module)

Workbooks.Open FileName:="bla.csv"
        Windows("Your.xls").Activate
        Range("A1").Value = Windows("blah.csv").Sheets("blah").Range("A1").Value

[LodBot]

I can open the file fine when I include the filepath before it. But, even if the file I want to open is in the same directory as the file that is executing the macro I need the path.

So my question is:

How can I get excel to open the file from the same directory w/ out including a path.

Thanks,
Alex

[opus]

Since you can't be sure the path to this directory is set in the path-section of your OS, you have to include the path.

[LodBot]

allright, now check this out... I can't get this code to compile:

dim filename as string
dim worksheet as string

filename = "C:\Documents and Settings\s118479\My Documents\training\_var_opt_psoft_prd_chr815_intf_out_tiperb32.csv"

worksheet = "_var_opt_psoft_prd_chr815_intf_"

Range("A1").Value = Windows(filename).Sheets(worksheet).Range("A1").Value

I get "Error number 9 - Subscript out of Range"

any ideas?

Thanks for all the help opus!
Alex

[opus]

Compiling?
Are you really using VBA or isn't it VB

Using VBA I have no problem running that lines, but they are NOT opening the file filename

Workbooks.Open filename:=filename

is missing. After that you need to reactivate the original file.

Windows("Your.xls").Activate

[LodBot]

I'm using VBA, by compiling I meant hitting F5. I tried the following lines of code:

dim filename as string
dim Worksheet as string

filename = "C:\Documents and Settings\s118479\My Documents\training\_var_opt_psoft_prd_chr815_intf_out_tiperb32.csv"

Worksheet = "_var_opt_psoft_prd_chr815_intf_"

Workbooks.Open filename:=filename
Windows("SW Training A(2)1.xls").Activate

Range("A1").Value = Windows(filename).Sheets(Worksheet).Range("A1").Value

and am still getting the same error

any ideas?

Thanks,
Alex

[opus]

Doesn't it tell where the error is?

[LodBot]

sorry, I should have posted that

the error exhists in this line:

Range("A529").Value = Windows(filename).Sheets(Worksheet).Range("A3").Value

I changed the cells so incase it DID work, no data would be overwritten.

Thanks for all the help dude!

[opus]

Found it, I think

you need to keep the path and filename seperate, since you need the path and filename for the .open statement and only the fielname when you refer to the already open workbook!

Maybe you can try to use

ChDir "C:\your path"

to change the directory and use filename just with your filename.

[LodBot]

Dim filename As String
Dim Worksheet As String
Dim filepath As String

filename = "_var_opt_psoft_prd_chr815_intf_out_tiperb32.csv"
filepath = "C:\Documents and Settings\s118479\My Documents\training\"

Worksheet = "_var_opt_psoft_prd_chr815_intf_"

Workbooks.Open filename:=filepath & filename
Windows("SW Training A(2)1.xls").Activate

Sheets("Software Personnel").Range("A529").Value = Windows(filename).Sheets(Worksheet).Range("A3").Value

I got the following error:

error '439' object doesn't support this property or method

and it didn't like this line:

Sheets("Software Personnel").Range("A529").Value = Windows(filename).Sheets(Worksheet).Range("A3").Value

Do you think maybe my excel install went bad?

Thanks for all the help dude,
Alex

[opus]

It looks like you can't read directly from the not activated file, try this (but since the error I got was '438' you might have another problem

Dim filename As String
Dim Worksheet As String
Dim filepath As String
Dim Value As String
 
filename = "_var_opt_psoft_prd_chr815_intf_out_tiperb32.csv"
filepath = "C:\Documents and Settings\s118479\My Documents\training\"
 
Worksheet = "_var_opt_psoft_prd_chr815_intf_"
 
Workbooks.Open filename:=filepath & filename
Value = Sheets(Worksheet).Range("A3").Value
Windows("SW Training A(2)1.xls").Activate
 
Sheets("Software Personnel").Range("A529").Value = Value

[LodBot]

that code ran! Now I'm getting an error that is really starting to piss me off!!!! Anyway, here's that STUPID line:

Workbooks.Open filename:="C:\training_dump\" & Range("A35").Value

The correct filename is in A35, all the filenames and paths are correct! The reason why I'm so iratated is because when I substitute "C:\training_dump\" & Range("A35").Value with "C:\training_dump\open.csv" it works! The only answer to this question that I could think of is that maybe when you read from a cell, a new line gets added at the end?

Any ideas?

Thanks,
Alex

One more thing: How do I close the document that I opened previously, but still keep the main document open? If that doesn't make sense: How would I close the file "blah.xls" w/ out closing "open.xls" Thanks

[opus]

Funny, that line works without problem for me!
To close a workbook use:

Workbooks("blah.xls").Close

[LodBot]

thanks