Sub ImportAllCsvInDirectory()
'
' Copied from: http://www.excelforum.com/excel-programming-vba-macros/504512-import-multiple-csv-files-into-current-workbook-as-separate-sheets.html
'
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
'Fill in the path\folder where the files are
'on your machine
'MyPath = "c:\Data"
MyPath = GetFolder("c:\")
'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If
'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.csv")
'If Not FilesInPath = False Then
If FilesInPath = "" Then
MsgBox "No files to consolidate"
Exit Sub
End If
On Error GoTo CleanUp
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0
' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With
mybook.Close savechanges:=False
Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True
SaveAsNewFile
End Sub
'
' Copied from: http://www.mrexcel.com/forum/excel-questions/294728-browse-folder-visual-basic-applications.html
'
Function GetFolder(strPath As String) As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function
'
'
Sub SaveAsNewFile()
Dim wb As Workbook
Dim NewFileName As String
Dim NewFileFilter As String
Dim myTitle As String
Dim FileSaveName As Variant
Dim NewFileFormat As Long
Set wb = ThisWorkbook
'Use following code to set to workbook other than this one
'Set wb = Workbooks("My Test Save As File.xlsm")
If Application.Version >= 12 Then 'Version 12 is xl2007
'Note: If file extension not included in B18 then concatenate it
'NewFileName = wb.Sheets("Sheet1").Range("B18").Value & ".xlsm"
NewFileFilter = "Excel Macro-Enable Workbook (*.xlsm), *.xlsm"
'The value 52 is substituted in next line for the constant _
xlOpenXMLWorkbookMacroEnabled because earlier versions of _
excel will not recognize the constant and code will error.
NewFileFormat = 52
Else
'Note: If file extension not included in B18 then concatenate it
'NewFileName = wb.Sheets("Sheet1").Range("B18").Value & ".xls"
NewFileFilter = "Excel 97-2003 Workbook (*.xls), *.xls"
'Because xlNormal is an earlier version constant, later versions _
of excel will recognize it.
NewFileFormat = xlNormal
End If
myTitle = "Navigate to the required folder"
FileSaveName = Application.GetSaveAsFilename _
(InitialFileName:=NewFileName, _
FileFilter:=NewFileFilter, _
Title:=myTitle)
If Not FileSaveName = False Then
wb.SaveAs Filename:=FileSaveName, _
FileFormat:=NewFileFormat
Else
MsgBox "File NOT Saved. User cancelled the Save."
End If
End Sub
'
'
Sub OpenFile()
Dim sFilename As String
sFilename = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If Not sFilename = False Then
Workbooks.Open sFilename
Else
MsgBox "File NOT Saved. User cancelled the Save."
End If
End Sub
Oracle sqlplus and instant client on Mac OS/X without DYLD_LIBRARY_PATH
URL: http://blog.caseylucas.com/2013/03/03/oracle-sqlplus-and-instant-client-on-mac-osx-without-dyld_library_path/Posted on
I recently needed to get sqlplus (11.2) running on a mac (10.8.2).
Oracle supports this via their instant client. For basic sqlpus, you
need the lite oracle instant client and oracle sqlplus instant client packages. If you try to run sqlplus after unzipping the files, you probably will see an error message similar to:
You can use otool to see the library locations embedded in the executables and libraries. Ex:
$ ./sqlplus dyld: Library not loaded: /ade/b/2649109290/oracle/sqlplus/lib/libsqlplus.dylib Referenced from: /Users/clucas/apps/instantclient_11_2/./sqlplus Reason: image not found Trace/BPT trap: 5This error can be fixed using one of the following methods:
- Move all the executables and supporting library files to locations which are searched by default (/usr/lib and /usr/bin.) See this stackoverflow answer.
- Set your DYLD_LIBRARY_PATH environment variable so that the sqlplus executable can find required oracle libraries and so that those libraries can also find their dependencies.
You can use otool to see the library locations embedded in the executables and libraries. Ex:
$ otool -L sqlplus sqlplus: /ade/b/2649109290/oracle/sqlplus/lib/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0) /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0) /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)I assume those /ade/… paths are from the machine oracle used for building the instant client software. We can replace these with @executable_path in order to have the dynamic loader find the libraries in the same directory where the executable is found. We want to end up with something like:
$ otool -L sqlplus sqlplus: @executable_path/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0) @executable_path/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0) @executable_path/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0) /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)Assuming you also adjust the libraries in the same directory, you’ll be able to run sqlplus just by having it in your PATH. You can run the following script in the directory where sqlplus is located. It will change all of the oracle dynamic library references to use @executable_path instead of the /ade/… full path:
#!/bin/sh
# script to change the dynamic lib paths and ids for oracle instant client
# exes and libs
# proces all the executable files in this directory
find . -maxdepth 1 -type f \( -perm -1 -o \( -perm -10 -o -perm -100 \) \) -print | while read exe
do
echo adjusting executable $exe
baseexe=`basename $exe`
otool -L $exe | awk '/oracle/ {print $1}' | while read lib
do
echo adjusting lib $lib
baselib=`basename $lib`
if [ "$baseexe" = "$baselib" ]
then
echo changing id to $baselib for $exe
install_name_tool -id $baselib $exe
else
echo changing path id for $lib in $exe
install_name_tool -change $lib @executable_path/$baselib $exe
fi
done
done
Once the script is run and it changes the libs and executables, you
can just add the directory holding sqlplus to your path and run sqlplus.
No need to set any other oracle environment variables like
ORACLE_HOME. Here’s the whole thing:$ mkdir example $ cd example /Users/clucas/apps/example $ unzip ~/dl/instantclient-basiclite-macos.x64-11.2.0.3.0.zip Archive: /Users/clucas/dl/instantclient-basiclite-macos.x64-11.2.0.3.0.zip inflating: instantclient_11_2/BASIC_LITE_README inflating: instantclient_11_2/adrci inflating: instantclient_11_2/genezi inflating: instantclient_11_2/libclntsh.dylib.11.1 inflating: instantclient_11_2/libnnz11.dylib inflating: instantclient_11_2/libocci.dylib.11.1 inflating: instantclient_11_2/libociicus.dylib inflating: instantclient_11_2/libocijdbc11.dylib inflating: instantclient_11_2/ojdbc5.jar inflating: instantclient_11_2/ojdbc6.jar inflating: instantclient_11_2/uidrvci inflating: instantclient_11_2/xstreams.jar $ unzip ~/dl/instantclient-sqlplus-macos.x64-11.2.0.3.0.zip Archive: /Users/clucas/dl/instantclient-sqlplus-macos.x64-11.2.0.3.0.zip inflating: instantclient_11_2/SQLPLUS_README inflating: instantclient_11_2/glogin.sql inflating: instantclient_11_2/libsqlplus.dylib inflating: instantclient_11_2/libsqlplusic.dylib inflating: instantclient_11_2/sqlplus $ export PATH=$PATH:~/apps/example/instantclient_11_2 $ sqlplus dyld: Library not loaded: /ade/b/2649109290/oracle/sqlplus/lib/libsqlplus.dylib Referenced from: /Users/clucas/apps/example/instantclient_11_2/sqlplus Reason: image not found Trace/BPT trap: 5 $ cd instantclient_11_2/ /Users/clucas/apps/example/instantclient_11_2 $ changeOracleLibs.sh adjusting executable ./adrci adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing path id for /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 in ./adrci adjusting lib /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib changing path id for /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib in ./adrci adjusting executable ./genezi adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing path id for /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 in ./genezi adjusting executable ./libclntsh.dylib.11.1 adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing id to libclntsh.dylib.11.1 for ./libclntsh.dylib.11.1 adjusting lib /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib changing path id for /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib in ./libclntsh.dylib.11.1 adjusting executable ./libnnz11.dylib adjusting lib /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib changing id to libnnz11.dylib for ./libnnz11.dylib adjusting executable ./libocci.dylib.11.1 adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libocci.dylib.11.1 changing id to libocci.dylib.11.1 for ./libocci.dylib.11.1 adjusting executable ./libociicus.dylib adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing path id for /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 in ./libociicus.dylib adjusting executable ./libocijdbc11.dylib adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing path id for /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 in ./libocijdbc11.dylib adjusting lib /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib changing path id for /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib in ./libocijdbc11.dylib adjusting executable ./libsqlplus.dylib adjusting lib /ade/b/2649109290/oracle/sqlplus/lib/libsqlplus.dylib changing id to libsqlplus.dylib for ./libsqlplus.dylib adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing path id for /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 in ./libsqlplus.dylib adjusting lib /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib changing path id for /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib in ./libsqlplus.dylib adjusting executable ./libsqlplusic.dylib adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing path id for /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 in ./libsqlplusic.dylib adjusting lib /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib changing path id for /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib in ./libsqlplusic.dylib adjusting executable ./sqlplus adjusting lib /ade/b/2649109290/oracle/sqlplus/lib/libsqlplus.dylib changing path id for /ade/b/2649109290/oracle/sqlplus/lib/libsqlplus.dylib in ./sqlplus adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing path id for /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 in ./sqlplus adjusting lib /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib changing path id for /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib in ./sqlplus adjusting executable ./uidrvci adjusting lib /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 changing path id for /ade/b/2649109290/oracle/rdbms/lib/libclntsh.dylib.11.1 in ./uidrvci adjusting lib /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib changing path id for /ade/b/2649109290/oracle/ldap/lib/libnnz11.dylib in ./uidrvci $ sqlplus SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 3 22:38:28 2013 Copyright (c) 1982, 2012, Oracle. All rights reserved. Enter user-name: ^C $ cd .. $ sqlplus SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 3 22:38:40 2013 Copyright (c) 1982, 2012, Oracle. All rights reserved. Enter user-name: ^C