Read closed csv file from excel 2000 vba

Question

I am using excel-vba to access a closed .csv file created by another program.

The code below just goes through each cell and copies the value from the closed excel file to same cell in the open workbook.
The first .formula line works with a .xls file, the commented out .formula line referencing a .csv file does not - I get "#REF!" in every cell.

I assume that the error that I get is due to the fact that I haven't stated it is not a "standard" .xls file?

The final code will read from a .csv file that is appended to every so often and therefore it has to remain closed.
The software that creates the .csv file is not able to output to .xls file. It will be coded so that my macro will resume from where it left off rather than doing the whole worksheet every time.

Any help would be much appreciated.

Sub GetData()

Dim RowNumber As Integer
Dim ColNumber As Integer

For RowNumber = 1 To 100
For ColNumber = 1 To 104    
    With Cells(RowNumber, ColNumber)
        .Formula = "='C:\Documents and Settings\admin\Desktop\[data.xls]Sheet1'!" & Cells(RowNumber, ColNumber).Address
        '.Formula = "='C:\Documents and Settings\admin\Desktop\[data1.csv]Sheet1'!" & Cells(RowNumber, ColNumber).Address
        .Value = .Value
    End With    
Next
Next

End Sub

You can use ADO. Another Alternative [HERE](http://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook/9261915#9261915) — Siddharth Rout, Apr 08 '14 at 11:35
It should work - check your sheet name in your `data1.csv` file. It is likely to be `data1` rather than `Sheet1` — brettdj, Apr 08 '14 at 11:38
You know that CSV is a text file that can be read with standard file functions? That way you don't need to open and lock it in Excel. I must admit I don't really understand your requirements. — Nick.Mc, Apr 08 '14 at 11:42
If you want to keep the file closed you might be in for more work than you planned on. I have a similar situation at work, I needed to set up a SyncToy job to copy the file to a new location and open that one. You might have to specify read only in your workbooks.open command. Also you have a most excellent name and that song is going to be stuck in my head all day. — Bmo, Apr 08 '14 at 11:45

brettdj · Accepted Answer · 2014-04-08T11:55:01.873

3

You coukd open the file directly and copy the range in a single shot

Change the file path below to suit.

This line opens the file as ReadOnly so it can be appended to elsewhere
Set Wb2 = Workbooks.Open("C:\temp\data.csv", False, True)

Sub WhyNotOpen()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Wb1 = ThisWorkbook
Set Wb2 = Workbooks.Open("C:\temp\data.csv", False, True)
Wb1.Sheets(1).Range("A1:CV104").Value = Wb2.Sheets(1).Range("A1:CV104").Value
Wb2.Close False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

edited Apr 08 '14 at 11:55

answered Apr 08 '14 at 11:44

brettdj

54,857
16
114
177

+ 1 Opening copying and closing sounds fast enough – Siddharth Rout Apr 08 '14 at 12:02
1

Opening as read only like this works without interfering with the other program. Good use of turning updating off as well I might add, I've seen m any programs grind to a halt when people have left it on. Thanks very much! – Oil Drum Alley Apr 08 '14 at 12:15

score 0 · Answer 2 · answered Apr 08 '14 at 11:37

you can open the csv and reference it as workbook object first before apply it, since the csv file was opened, you can just place file name and sheet name in reference.

Sub GetData()

Dim wb as workbook

Dim RowNumber As Integer
Dim ColNumber As Integer

set wb = application.workbooks.open("C:\Documents and Settings\admin\Desktop\data1.csv")

For RowNumber = 1 To 100
 For ColNumber = 1 To 104

    With Cells(RowNumber, ColNumber)
        .Formula = "='[data1.csv]Sheet1'!" & Cells(RowNumber, ColNumber).Address
        .Value = .Value
    End With

 Next
Next

End Sub

or replace formula with more generic syntax like before

.Formula = "='" &"[" & wb.Name & "]" & wb.Sheets(1).Name &"'!" & Cells(RowNumber, ColNumber).Address

i overlook the question and thought author would like to update each cell formula, you are right. — mememoremore, Apr 08 '14 at 11:55

Read closed csv file from excel 2000 vba

2 Answers2