- #TRANSPOSE AN ARRAY IN EXCEL VBA HOW TO#
- #TRANSPOSE AN ARRAY IN EXCEL VBA UPDATE#
- #TRANSPOSE AN ARRAY IN EXCEL VBA CODE#
In the past, I've opened a file, had a function create an array using it's contents and output that back to a variable that called the function. The contents of the csv file, aside from the header, are alphanumeric characters in column A (security ISINs for specifics) and mixed number values (doubles) in column B.
#TRANSPOSE AN ARRAY IN EXCEL VBA CODE#
The existing code opens the file, reads the data contents into a variant, closes csv file and then prints to the Main Workbook before further processing. csv file and data is imported back into the Main workboook. The existing code generates a Unix script (which I manually run in separate system) which in turn generates a.
#TRANSPOSE AN ARRAY IN EXCEL VBA UPDATE#
It's some legacy code that I'm trying to update at work. I am not sure (VERY limited tries with ADO), but I think you want to close the recordset after that line.
If by chance you want to Transpose inside the function, not tested, but I think f_ADO_Out = Application.Transpose(objRecordset.GetRows) should work. Have your Locals window showing and you can see the returned arr(). Option Explicit Sub example()Dim arr arr = MyFunction Stop End Sub Function MyFunction() As VariantDim ary(0 To 1, 0 To 5000)Dim n As Long For n = 0 To 5000 ary(0, n) = n + 1 ary(1, n) = (n + 1) * 20 Next MyFunction = Application.Transpose(ary) End Function
You will not need Option Base 1 if you Transpose the data, as Transpose returns a 1-based array. I am not exactly sure what you are wanting, as shg's would seem to be what you wanted?
#TRANSPOSE AN ARRAY IN EXCEL VBA HOW TO#
I also tried to transpose within the function but I get the same error thoughts on how to fix please? I'm passing valid arguments into the function and do not believe the inputs are the problem. ObjRecordset.Open Replace(sql, strName), objConnection, adOpenStatic, adLockOptimistic, adCmdTextĮnd FunctionRealised the output from the function is transposed and because this is something I'm bolting into existing code, I prefer to transpose before using the data. Sql = "Select * FROM 'sql = "Select * FROM " 'SQL query to set up a "table of data" to read into Excel ObjConnection.Open "Provider=.4.0 " & "Data Source=" & strPath & " " & "Extended Properties=""text HDR=No FMT=Delimited """ Set objRecordset = CreateObject("ADODB.Recordset") Set objConnection = CreateObject("ADODB.Connection") HuiA,12345,HuiC,HuiF,HuiG,Account numb,11:56:43 AM,oice Number,Date of,issue,of issue I've also played with the size of the output range and it makes no difference, though as far as I can see it shouldn't make any difference, ie if it's larger than the array it should just fill the 'empty' cells with #N/A, and if it's smaller the output would be truncated The source file contains proprietary info so I can't share it, but any text file should suffice with a little bit of effort With Range(Cells(2, 1), Cells(UBound(myarray, 2) + 1, 1))Įnd WithThe vars are all strings except where other wise state, eg date/time Loop Until InStr(MyRecordIn, " END OF REPORT ") Or EOF(1)
0 kommentar(er)
