Pull data from closed workbook

[ceytl]

Hello,

This VBA code I use works great, but I would like it to switch from row to column.

I want to start in column C with row 1

Would anyone know how to do this?

Thanks,

Sub PullDatafomClosedWB()
'
'   This macro will get the first alphabetical sheet name from a closed xlsx workbook and then get data from that sheet name.
'   It works with numbers, spaces and ' Workbook remains closed the entire time.
'
    Dim SourceDirectory As String, SourcefileName As String, DestinationSheetName As String, SourceSheetName As String
    Dim DestinationRow  As Long, MyCell As Range
    Dim conexion        As Object
    Dim objCat          As Object
'
    Application.ScreenUpdating = False
 '
    Set conexion = CreateObject("adodb.connection")
    Set objCat = CreateObject("ADOX.Catalog")
'
    DestinationRow = 1                                                                  ' <--- Set this to the top row for the results
'
    SourceDirectory = ActiveWorkbook.Path & "\Booking Orders\"
'
    DestinationSheetName = "Data"                                                     ' <--- Set this to the Destination Sheet Name
    SourcefileName = Dir(SourceDirectory & "*.xlsx")                                    ' Save source file name

    Do While SourcefileName <> ""
        DestinationRow = DestinationRow + 1
'
        conexion.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & SourceDirectory & SourcefileName & "; Extended Properties=""Excel 12.0; HDR=YES"";"
'
        Set objCat.ActiveConnection = conexion
        SourceSheetName = Replace(objCat.Tables(0).Name, "$", "")
        SourceSheetName = Replace(SourceSheetName, "'", "")

        conexion.Close
'
        Set MyCell = ThisWorkbook.Sheets(DestinationSheetName).Cells(DestinationRow, "B")
        MyCell.Offset(, 0).Formula = "='" & SourceDirectory & "[" & SourcefileName & "]" & SourceSheetName & "'!B5"
        MyCell.Offset(, 1).Formula = "='" & SourceDirectory & "[" & SourcefileName & "]" & SourceSheetName & "'!B8"
        MyCell.Offset(, 2).Formula = "='" & SourceDirectory & "[" & SourcefileName & "]" & SourceSheetName & "'!B15"
        MyCell.Offset(, 3).Formula = "='" & SourceDirectory & "[" & SourcefileName & "]" & SourceSheetName & "'!B3"
        MyCell.Offset(, 5).Formula = "='" & SourceDirectory & "[" & SourcefileName & "]" & SourceSheetName & "'!B10"

''        MyCell.Resize(1, 5).Value = MyCell.Resize(1, 5).Value
        
        SourcefileName = Dir
    Loop
'
    Set objCat = Nothing
    Set conexion = Nothing
'
    Application.ScreenUpdating = True
End Sub

 

[Marc L]

Hello,​

​

use Application.Transpose for example to switch ADO columns format to Excel rows …​

 

[ceytl]

Hello,​

​

use Application.Transpose for example to switch ADO columns format to Excel rows …​

Where would I put Application.Transpose in the code?

Thanks!

 

[Marc L]

​

Where you read your data : so it seems directly in the formula so just adding TRANSPOSE( ) within if really necessary …​

 

[ceytl]

​

Where you read your data : so it seems directly in the formula so just adding TRANSPOSE( ) within if really necessary …​

I'm not sure what I am doing wrong, I tried this, but it didn't work.

MyCell.Offset(, 2).Formula.Transpose() = "='" & SourceDirectory & "[" & SourcefileName & "]" & SourceSheetName & "'!G27"

Do I need to add anything, or put it somewhere else?

 

[Marc L]

​

No, inside the formula string … But in fact as you are reading only a single cell so TRANSPOSE is very not necessary here, my bad …​

I was thinking about it when using contiguous cells in a range.​