Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where's a VBS reference?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where's a VBS reference?
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 "Unknown" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where's a VBS reference?
Here you go. Also note the VBA is quite different than VBScript
http://www.microsoft.com/downloads/d...displaylang=en -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Unknown" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where's a VBS reference?
Some useful VBScript / Excel links:
http://www.microsoft.com/technet/scr...da/office.mspx http://www.microsoft.com/technet/scr...bs/office.mspx -- urkec "Unknown" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Where's a VBS reference?
Thank you very much for your helpful answers guys. Especially thank you for the script you took time to make. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Column letter reference as number reference | Excel Programming | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
Macro to Reference Column Next to Current Reference | Excel Discussion (Misc queries) |