Macro creating Comma separated Key

[fbenzoni]

Hi,
i have an issue with a macro i'm using. the aim of the macro is to create a comma separated key based on cells selected and paste it in a specific cell. the issue I'm now having is related to the fact that cells have a limit of 32.767 chars so if the user is selecting a list that, concatenated, is longer than the cell limit, some chars will be lost. what i'm struggling to find is a way to split the list, let's say every 3000 cells, and create multiple keys(<3000 than 1 key, 3001<>5999 than 2 keys, etc). is there anyone that can support here? if needed i can attach a sample workbook


Sub Key()
Dim arr
With Sheets("Key")
Selection.SpecialCells(12).Copy .[A1]
arr = Join(Application.Transpose(.[A1].CurrentRegion), ",")
.[A1].CurrentRegion.ClearContents
.[A1].Value = arr
End With
Sheets("Key").Activate
End Sub





thanks for the support!

[Paul_Hossler]

Why do you need / want such as long key?

http://khoiriyyah.blogspot.com/2012/06/vb6-hash-class-md5-sha-1-sha-256-sha.html

Has a nice example of creating Hash strings. Maybe before putting the 'key' into A1, you could Hash it, and then Hash user entered passwords (?) and compare

[fbenzoni]

it's needed since the reporting tool i use accepts comma separated values only so need to find a way to convert values i have in a single column, multiple rows, into a single cells with comma separated values

[snb]

You can save any (part of a) worksheet as a CSV-file.

[Paul_Hossler]

Well, you can try this.

Ii makes a CSV file with one very long record with the values from A1 to end of data in A1



Option Explicit


Sub LongCSV()
Dim sCSV As String
Dim iFileNum As Long
Dim sFilename As String

sFilename = Environ("USERPROFILE") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "Something.csv"


sCSV = Join(Application.WorksheetFunction.Transpose(ActiveSheet.Cells(1, 1).CurrentRegion), ",")

iFileNum = FreeFile
Open sFilename For Output As iFileNum
Print #iFileNum, sCSV
Close #iFileNum
End Sub

[fbenzoni]

thanks Paul, would it be possible to generate a csv starting from the macro i posted? this because my macro works based on a selection of data done by the user and is therefore dynamic

[Paul_Hossler]

Add ability to handle values with embedded commas and just selected cells



Option Explicit


Sub LongCSV()
Dim sCSV As String
Dim iFileNum As Long
Dim sFilename As String
Dim r As Range
Dim s As String

If Not TypeOf Selection Is Range Then Exit Sub

If Selection.Cells.Count = 0 Then Exit Sub

For Each r In Selection.Cells
s = Trim(r.Value)
If Len(s) > 0 Then
If InStr(s, ",") > 0 Then
sCSV = sCSV & """" & s & """" & ","
Else
sCSV = sCSV & s & ","
End If
End If
Next

If Right(sCSV, 1) = "," Then sCSV = Left(sCSV, Len(sCSV) - 1)


sFilename = Environ("USERPROFILE") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "Something.csv"

iFileNum = FreeFile
Open sFilename For Output As iFileNum
Print #iFileNum, sCSV
Close #iFileNum
End Sub

[snb]

Or:


Sub M_snb()
Sheet1.Copy
ActiveWorkbook.SaveAs "G:\OF\__today.csv", 23
ActiveWorkbook.Close 0
End Sub

[Paul_Hossler]

@snb ---

The way I read the OP's #1 post ...



the aim of the macro is to create a comma separated key based on cells selected and paste it in a specific cell. the issue I'm now having is related to the fact that cells have a limit of 32.767 chars so if the user is selecting a list that, concatenated, is longer than the cell limit,


... it sounds like (for some reason) they're looking for a single very long string


29622


Not sure why, but some external legacy systems do have peculiar interface requirements

Normally, I'd do a CSV (or TDL) file with one cell per records like your macro

[snb]

@PH

Yes, you are probably right.
But, to be honest: why such a format ? Which programmer creates such a requirement ?

[Paul_Hossler]

:dunno

I've never seen anything like it before

[snb]

But if the amount or characters in an Excel sheet should be insuffient, it would be wiser not to use Excel for this 'key' in the first place. A .txt file would be more appropriate, I think.


Sub M_snb()
sheet1.usedrange.copy
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
createobject("scripting.filesystemobject").createtextfile("G:\OF\key.txt").write replace(replace(.GetText,vbtab,","),vbCrLf,",")
End With
End Sub

PS. It has probably to do with 'mining' and blockchains.:think: