Where's a VBS reference?

[Unknown]

Hi,

I'm just wondering if there's some sort of reference somewhere that I
can use to figure out what available script functions there are in
Excel.

I'm interested in making a macro that does the following:

1) Selects a range of cells
2) Saves the selected range of cells to a CSV file
3) The saving process should be completely transparent and not change
the workspace format (in other words, the file currently open in Excel
(even after the save to CSV) should still be the XLS file)

I would bind this macro to a button inside of some random cell in the
document

I don't know VB Script that well, so it would be a learning process
for me. It would help me get this task done quicker if I knew of some
sort of documentation for the Excel script functions (functions to
select cell ranges, save out cells to a CSV file, etc)

Thanks.

 

[RB Smissaert]

You don't need VBS for that, VBA can do it:

Sub RangeToText()

Dim arr
Dim strBookName As String
Dim strFile As String

strBookName = Replace(ActiveWorkbook.Name, ".xls", ".csv", 1, -1,
vbTextCompare)

'for un-saved workbooks
If InStr(1, strBookName, ".csv", vbBinaryCompare) = 0 Then
strBookName = strBookName & ".csv"
End If

strFile = "C:\" & strBookName

If bFileExists(strFile) Then
If MsgBox(strFile & _
vbCrLf & vbCrLf & _
"Already exists, overwrite this file?", vbYesNo, _
"save range to text file") = vbNo Then
Exit Sub
End If
End If

arr = ActiveWindow.RangeSelection

SaveArrayToText strFile, arr

End Sub

Sub SaveArrayToText(ByVal txtFile As String, _
ByRef arr As Variant, _
Optional ByVal LBRow As Long = -1, _
Optional ByVal UBRow As Long = -1, _
Optional ByVal LBCol As Long = -1, _
Optional ByVal UBCol As Long = -1, _
Optional ByRef fieldArr As Variant)

Dim r As Long
Dim c As Long
Dim hFile As Long

If LBRow = -1 Then
LBRow = LBound(arr, 1)
End If

If UBRow = -1 Then
UBRow = UBound(arr, 1)
End If

If LBCol = -1 Then
LBCol = LBound(arr, 2)
End If

If UBCol = -1 Then
UBCol = UBound(arr, 2)
End If

hFile = FreeFile

'Close before reopening in another mode.
'---------------------------------------
On Error Resume Next
Open txtFile For Input As hFile
Close #hFile

Open txtFile For Output As hFile

If IsMissing(fieldArr) Then
For r = LBRow To UBRow
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, arr(r, c)
Else
Write #hFile, arr(r, c);
End If
Next c
Next r
Else
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, fieldArr(c)
Else
Write #hFile, fieldArr(c);
End If
Next c
For r = LBRow To UBRow
For c = LBCol To UBCol
If c = UBCol Then
Write #hFile, arr(r, c)
Else
Write #hFile, arr(r, c);
End If
Next c
Next r
End If

Close #hFile

End Sub

Public Function bFileExists(strFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(strFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function


RBS

 

[Dave Patrick]

Here you go. Also note the VBA is quite different than VBScript

http://www.microsoft.com/downloads/...2e-4877-4563-8e83-4848dda796e4&displaylang=en

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Guest]

Some useful VBScript / Excel links:

http://www.microsoft.com/technet/scriptcenter/resources/qanda/office.mspx

http://www.microsoft.com/technet/scriptcenter/hubs/office.mspx

 

[Unknown]

Thank you very much for your helpful answers guys. Especially thank
you for the script you took time to make.