I have tried a few variations, looked at youtube videos and looked to CHATGPT.  I have two options on the dropdown for file destination: (1) My Choice; (2) a predefined SharePoint location.  I have had the “My Choice” option working for some time and added option 2 which is the problem area. 

I get through to the stream.SaveToFile statement and I see a spinner pop up and about 10 seconds later, it returns a 3004 error.

I have tried both http:// and changing it to a network directory setup (CHATGPT) so it reads: \mycompany.sharepoint.comsitesOBOSitePagesCollabHomeOBO_STG_Export_Files.  The HTTP address of the sharepoint folder is:  https://mycompany.sharepoint.com/sites/OBO/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsites%2FOBO%2FShared%20Documents%2FOBO%5FQA%5FExport%5FFiles&viewid=a7e2c126%2Dddda%2D4583%2D8ae2%2D55346c1a7cbf

The code is below.  It seems to be how I am forming things in the stream.SaveToFile

Destination filename would be something like: banner-product-20240529142717

Sub ExportToCSVCheckBox(ws_base As String <- this equates to “product”)
Dim src_ws As Worksheet
Dim create_csvs As Worksheet
Dim rng As Range
Dim cell As Range
Dim filePath As String
Dim rowIndex As Long
Dim colIndex As Long
Dim line As String
Dim delimiter As String
Dim banner As Variant
Dim outputSelection As String
Dim csv_filename As String
Dim expFile As Variant
Dim timeStamp As String
Dim stream As Object ‘ ADODB.Stream

‘
‘ This subroutine provides the following capabilities:
‘ 1) Reads cells from Create_CSVs worksheet for banner, which files to export and where to
‘ place the files
‘ 2) Generate the output in UTF-8 format to allow for special French characters
‘ 3) Blanks out any 0 filled cells provided by Excel – CCv2 does not like them
‘ 4) Replaces traditional “,” delimited file with “|” – CCv2 wants it that way
‘ 5) Puts double quotes “” around cell contents to deal with those cell contents that
‘ have double quotes in them. “12” Latex Balloon” needs to be “12”” Latex Balloon” so
‘ that is shows in CCv2 as 12″ Latex Balloon
‘

On Error GoTo ErrorHandler

‘ Set the worksheet location for the input variables
Set create_csvs = ThisWorkbook.Sheets(“Create_CSVs”)

‘ Get the banner
banner = create_csvs.Range(“B3”).Value

‘ Get the output location
outputSelection = create_csvs.Range(“B17”)

‘ Get the expFile area
expFile = banner & “-” & ws_base & csv_filename

‘ Get the current date and time stamp
timeStamp = Format(Now, “yyyymmddhhmmss”)

‘ Create the filename for the CSV
csv_filename = expFile & “-” & timeStamp & “.csv”

‘ Set the name of the worksheet that holds the data for export
Set src_ws = ThisWorkbook.Sheets(expFile)

‘ Define the file path for the CSV file
If outputSelection = “My Choice” Then
filePath = Application.GetSaveAsFilename(InitialFileName:=csv_filename, FileFilter:=”CSV Files (*.csv), *.csv”)
‘ Check if user cancelled the Save As dialog
If filePath = “False” Then
Exit Sub
End If
Else ‘ assume we have a SharePoint location
filePath = create_csvs.Range(“C18”) & csv_filename
End If
‘ Define the range
Set rng = src_ws.UsedRange ‘ Adjust as needed

‘ Define the custom delimiter
delimiter = “|”

‘ Create the ADODB.Stream object
Set stream = CreateObject(“ADODB.Stream”)
stream.Type = 2 ‘ Specify stream type – we want To save text/string data.
stream.Charset = “UTF-8” ‘ Specify charset For the source text data.

‘ Open the stream
stream.Open

‘ Loop through each row in the range
For rowIndex = 1 To rng.Rows.Count
line = “”

‘ Loop through each column in the row
For colIndex = 1 To rng.Columns.Count
‘ Get the cell value
Set cell = rng.Cells(rowIndex, colIndex)

If cell.Value <> “” And cell.Value <> 0 Then ‘ Cannot have 0 values for IMPEX
line = line & “””” & cell.Value & “”””
End If
If colIndex < rng.Columns.Count Then
‘ Append the cell value to the line with the custom delimiter
line = line & delimiter
End If
Next colIndex

‘ Write the line to the stream
stream.WriteText line & vbCrLf
Next rowIndex
MsgBox “filepath = ” & filePath
‘ Save the stream to a file
stream.SaveToFile filePath, 2 ‘ 2 = adSaveCreateOverWrite

‘ Close the stream
stream.Close

‘ Notify the user that the export is complete
MsgBox “Data has been exported to ” & filePath, vbInformation
Exit Sub

ErrorHandler:
MsgBox “An error occurred: ” & Err.Description & ” (Error Number: ” & Err.Number & “)”
End Sub

​I have tried a few variations, looked at youtube videos and looked to CHATGPT.  I have two options on the dropdown for file destination: (1) My Choice; (2) a predefined SharePoint location.  I have had the “My Choice” option working for some time and added option 2 which is the problem area. I get through to the stream.SaveToFile statement and I see a spinner pop up and about 10 seconds later, it returns a 3004 error.I have tried both http:// and changing it to a network directory setup (CHATGPT) so it reads: \mycompany.sharepoint.comsitesOBOSitePagesCollabHomeOBO_STG_Export_Files.  The HTTP address of the sharepoint folder is:  https://mycompany.sharepoint.com/sites/OBO/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsites%2FOBO%2FShared%20Documents%2FOBO%5FQA%5FExport%5FFiles&viewid=a7e2c126%2Dddda%2D4583%2D8ae2%2D55346c1a7cbfThe code is below.  It seems to be how I am forming things in the stream.SaveToFileDestination filename would be something like: banner-product-20240529142717Sub ExportToCSVCheckBox(ws_base As String <- this equates to “product”)Dim src_ws As WorksheetDim create_csvs As WorksheetDim rng As RangeDim cell As RangeDim filePath As StringDim rowIndex As LongDim colIndex As LongDim line As StringDim delimiter As StringDim banner As VariantDim outputSelection As StringDim csv_filename As StringDim expFile As VariantDim timeStamp As StringDim stream As Object ‘ ADODB.Stream” This subroutine provides the following capabilities:’ 1) Reads cells from Create_CSVs worksheet for banner, which files to export and where to’ place the files’ 2) Generate the output in UTF-8 format to allow for special French characters’ 3) Blanks out any 0 filled cells provided by Excel – CCv2 does not like them’ 4) Replaces traditional “,” delimited file with “|” – CCv2 wants it that way’ 5) Puts double quotes “” around cell contents to deal with those cell contents that’ have double quotes in them. “12” Latex Balloon” needs to be “12”” Latex Balloon” so’ that is shows in CCv2 as 12″ Latex Balloon’On Error GoTo ErrorHandler’ Set the worksheet location for the input variablesSet create_csvs = ThisWorkbook.Sheets(“Create_CSVs”)’ Get the bannerbanner = create_csvs.Range(“B3”).Value’ Get the output locationoutputSelection = create_csvs.Range(“B17”)’ Get the expFile areaexpFile = banner & “-” & ws_base & csv_filename’ Get the current date and time stamptimeStamp = Format(Now, “yyyymmddhhmmss”)’ Create the filename for the CSVcsv_filename = expFile & “-” & timeStamp & “.csv”‘ Set the name of the worksheet that holds the data for exportSet src_ws = ThisWorkbook.Sheets(expFile)’ Define the file path for the CSV fileIf outputSelection = “My Choice” ThenfilePath = Application.GetSaveAsFilename(InitialFileName:=csv_filename, FileFilter:=”CSV Files (*.csv), *.csv”)’ Check if user cancelled the Save As dialogIf filePath = “False” ThenExit SubEnd IfElse ‘ assume we have a SharePoint locationfilePath = create_csvs.Range(“C18”) & csv_filenameEnd If’ Define the rangeSet rng = src_ws.UsedRange ‘ Adjust as needed’ Define the custom delimiterdelimiter = “|”‘ Create the ADODB.Stream objectSet stream = CreateObject(“ADODB.Stream”)stream.Type = 2 ‘ Specify stream type – we want To save text/string data.stream.Charset = “UTF-8” ‘ Specify charset For the source text data.’ Open the streamstream.Open’ Loop through each row in the rangeFor rowIndex = 1 To rng.Rows.Countline = “”‘ Loop through each column in the rowFor colIndex = 1 To rng.Columns.Count’ Get the cell valueSet cell = rng.Cells(rowIndex, colIndex)If cell.Value <> “” And cell.Value <> 0 Then ‘ Cannot have 0 values for IMPEXline = line & “””” & cell.Value & “”””End IfIf colIndex < rng.Columns.Count Then’ Append the cell value to the line with the custom delimiterline = line & delimiterEnd IfNext colIndex’ Write the line to the streamstream.WriteText line & vbCrLfNext rowIndexMsgBox “filepath = ” & filePath’ Save the stream to a filestream.SaveToFile filePath, 2 ‘ 2 = adSaveCreateOverWrite’ Close the streamstream.Close’ Notify the user that the export is completeMsgBox “Data has been exported to ” & filePath, vbInformationExit SubErrorHandler:MsgBox “An error occurred: ” & Err.Description & ” (Error Number: ” & Err.Number & “)”End Sub  Read More

​