Importing a csv File to a range

[SDruley]

Sub PortToStage()
Dim PicassoPage As Range
Set PicassoPage = Application.Range("BANKA")
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

Rick Rothstein back in 2009 was responsible for creating the above code. I have modified it to include my specific range name, "PicassoPage" where previously it indicated "Selection".
I hope I don't get into trouble on my first post on this site but I have seen Rick's work and it is my opinion he is the best programmer in the country.

So, I am looking for help in creating code to take the above generated file (PicassoPg.cvs) created using Workbook A and pull it into a range with the same name and configuration in Workbook B, sort of like a parallel universe thing. Both workbooks have easy access to the csv file and when you look at the corresponding worksheets in both workbooks they look identical. The only difference is that the range name in workbook B is "BankB"



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[snb]

writing:


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

for j=1 to ubound(sn)
c01=c01 & vbcrlf & join(WorksheetFunction.Transpose(application.index (sn,j))), ",")
next

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

reading:


Sub M_snb()
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
End Sub

[SDruley]

Oh my goodness, here we go. The best programmer, without question, in Europe comparing results with the best programmer in the US. Oh how lucky I am to have the benefit of this talent!

Snb and Fox thanks so much for you input. I will incorporate and let you know.

Steve



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

Sub PortToStage()
Dim PicassoPage As Range
Set PicassoPage = Application.Range("BANKA")
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

Rick Rothstein back in 2009 was responsible for creating the above code. I have modified it to include my specific range name, "PicassoPage" where previously it indicated "Selection".
I hope I don't get into trouble on my first post on this site but I have seen Rick's work and it is my opinion he is the best programmer in the country.

So, I am looking for help in creating code to take the above generated file (PicassoPg.cvs) created using Workbook A and pull it into a range with the same name and configuration in Workbook B, sort of like a parallel universe thing. Both workbooks have easy access to the csv file and when you look at the corresponding worksheets in both workbooks they look identical. The only difference is that the range name in workbook B is "BankB"
First, I must tell you that I am flattered beyond words at your generous comments about my programming abilities. Truth be told, I personally think you have greatly over-estimated my programming skills; but, that notwithstanding, I thank you profusely for your comments!

Second, change the file extension from the "cvs" you show to "csv" which is the proper extension for a comma-separated-values file. Once you have done that, you can simply open the file directly into your new workbook... no VBA code needed... and the file will look just like the original it was created from. When you open it, make sure to select "Text Files (*.prn; *.txt; *.csv)" in the "Files of type:" drop-down.




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[SDruley]

snb,

I got a syntax error in the following line of code


c01=c01 & vbcrlf & join(WorksheetFunction.Transpose(application.index (sn,j))), ",")


Can you remedy this for me?






https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[snb]

A simple bracket count:


c01=c01 & vbcrlf & join(WorksheetFunction.Transpose(application.index (sn,j)), ",")

BTW. If both workbooks are loaded and the ranges have the same size:


sub m_snb()
Workbooks("B.xlsm").Names("Banka").RefersToRange = Workbooks("A.xlsm").Names("Banka").RefersToRange.Value
end sub



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[SDruley]

Rick,

I got the file extension incorrect because I have been to the CVS drug store 1 too many times.
One item of note, is that I need to programmatically place the contents of your CSV file into Workbook B but done in the 67 milliseconds it took for your original code to create the file. I was assuming that the code would appear similar in structure. In other words, to programmatically open the file and do a copy and paste, like i would probably do, would take too long.

Hey, you and snb are the best. I know talent when I see it

[SDruley]

snb

Thanks for your wonderful input. I tried the correction to the line of code and now getting run-time error 5, invalid procedure call or argument.
Workbook A and B are each in their own instance of Excel for a long list of reasons. So, your code to read the file is very important to me.



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

Rick,

I got the file extension incorrect because I have been to the CVS drug store 1 too many times.
One item of note, is that I need to programmatically place the contents of your CSV file into Workbook B but done in the 67 milliseconds it took for your original code to create the file. I was assuming that the code would appear similar in structure. In other words, to programmatically open the file and do a copy and paste, like i would probably do, would take too long.

How about something like this...

Sub OpenPicassoPg()
Workbooks.Open "c:\Temp\PicassoPg.csv", Format:=2
End Sub





https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[SDruley]

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.

Steve


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[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
' ||||||||||||||||||||||||||||||||||||||||||||

[snb]

I assume the title of this thread doesn't represent it's content anymore ?

[SDruley]

Let me submit that the inclusion of a query approach represents the solution to the title of this thread and that this thread is closed