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

[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

[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

[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

[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

[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

[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

[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