Importing a csv File to a range

[snb]

Use:

Sub M_snb()
   sn = Application.Range("BANKA")

   For j = 1 To UBound(sn)
     c01 = c01 & vbCrLf & Join(Application.Index(sn, j), ",")
   Next

   CreateObject("scripting.filesystemobject").createtextfile("c:\TTND\PicassoPg.csv").write Mid(c01, 3)
End Sub

If the range contains errors (resulting from formulae) it will error out.
In that case you have to remove those first.

You can use a querytable in the second workbook to keep it updated with the csv file.
I think you can have a querytable connection directly to the workbook in the other Excel instance.

[SDruley]

With the help of two Gurus I have finalized on the following code:

' GENERATE INCOMING CSV
' |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sub PortToStage()
Dim PicassoPage As Range
Set PicassoPage = Application.Range("BANKB")
Dim X As Long, FF As Long, S() As String
ReDim S(1 To PicassoPage.Rows.Count)
For X = 1 To PicassoPage.Rows.Count
S(X) = Join(WorksheetFunction.Transpose(WorksheetFunction. _
Transpose(PicassoPage.Rows(X).Value)), ",")
Next
FF = FreeFile
Open "c:\TTND\PicassoPg.cvs" For Output As #FF
Print #FF, Join(S, vbNewLine)
Close #FF
End Sub
' ||||||||||||||||||||||||||||||||||||||||||||||||||||||||

' GET INCOMING DATA
' ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Application.Calculation = xlCalculationManual
sn = Split(CreateObject("scripting.filesystemobject").opentextfile("c:\TTND\PicassoPg.csv").readall, vbCrLf)
For J = 1 To Application.Range("BANKA").Rows.Count
  Application.Range("BANKA").Rows(J) = Split(sn(J - 1), ",")
Next
Application.Calculation = xlCalculationAutomatic
' ||||||||||||||||||||||||||||||||||||||||||||||||||||||||

[snb]

I tested a querytable from 1 Excelinstance to a workbook in the second Excel instance: no problem.

[Rick Rothstein]

Rick,

That was a fast open but is there a way to open the contents of the csv file into Application.Range("BANKA"). I don't have CPU time to do a cut and paste. Wow you are up early.

Actually, I wasn't up early... I had not gone to sleep for the "night" yet. Based on a past message, I assume you mean BANKB, not BANKA (anyway, that is what the code below assumes). See if this code (run from the workbook with BANKB defined in it) works...

Sub Import_BANKA_DataFileInto_BANKB()
  Dim FileNum As Long, TotalFile As String
  FileNum = FreeFile
  Open "c:\Temp\PicassoPg.csv" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Application.ScreenUpdating = False
  With Range("BANKB").Columns(1)
    .Value = WorksheetFunction.Transpose(Split(TotalFile, vbNewLine))
    .TextToColumns Comma:=True
  End With
  Application.ScreenUpdating = True
End Sub

[SDruley]

Rick,

Thank you for the code to retreive the csv file and place the contents into the primary workbook. As it turns out this code took a little over 2 seconds to complete while snb's solution was .24 seconds. So I will keep your solution on creating the csv file and snb's solution for reading it.

[snb]

In the attachment you'll find a zip file, containing BankA.xlsx and BankB.xlsm

Unpack them in the same directory.
Open each file in a separate instance of Excel.
Change something in the named range 'BankA" in the file 'Banka.xlsx';
Activate file 'bankB.xlsm' in the other Excel instance and check whether the changes have come through.

[SDruley]

snb,

Nice work. Can you give me a few steps to follow to create this linkage?

[snb]

create a new file
create a named range in the file
save the file :banka.xslx
close the file

create a new file
saveas Bankb.xlsm
ribbon / tab data
From Access
(filefilter 'all files')
select Banka.xlsx; open
select the named range : enter.
ready

[SDruley]

snb,

The quasi Access querytable has blown everything out of the water. 50 lines of code are gone, there are no csv files to create or read and the response time is as though both workbooks were part of the same worksheet. I can't believe my eyes!
I would show you the code but there isn't any. OMG

Steve

[SDruley]

Oh wait there is some code

' REFRESH DATA PORTAL
' ||||||||||||||||||||||||||||||||||||||||||||
ActiveWorkbook.RefreshAll ' 0.3 seconds
' ||||||||||||||||||||||||||||||||||||||||||||