VBA Excel code to retrieve data without opening a file

Question

I'm trying to get data from an excel workbook which is updated every month and the name of the file changes according to the date - I have an instructions page using the today function which gives me the month (this are the cell I'm referencing in "Month")

Problem is, the file I'm opening is very very big, and so this takes over 5 minutes just to start excel and copy the data. Is there anyway to modify my code to get the data without opening the excel file?

This is my code so far -

Sub UploadData()

Dim Model As Workbook
Dim Q As Workbook
Dim rngFX As Range
Dim Month As String

Set Model = ActiveWorkbook


Set Q = Workbooks.Open(Filename:=Sheets("Instructions").Range("$C$29").Value)

Month = ("C" & (Model.Sheets("Instructions").Range("$C$23")))

With Q
    With .Sheets(Month & " Summary")
        Set rngFX = .Range("A61:R66")
        rngFX.Copy Destination:=Model.Sheets("FOREX Forecast").Range("A3")
    End With
End With

Q.Close savechanges:=False

With Model.Sheets("FOREX Forecast").UsedRange
.Value = .Value 
End With

End Sub

Edit: I've added a picture of the error I'm getting - When I press debug it highlights this line:

  Rs.Open strSQL, strConn

If your Excel file has a fixed table-like structure, then you could access it via ADODB connection. Check, for example, this question: stackoverflow.com/questions/18144838/… — Alexey
– Alexey, Commented Dec 11, 2018 at 14:36
You can't copy or paste without opening the file, since both of those are operations that are child methods of the Workbook object. You need to use another method like ADODB :) — David Zemens
– David Zemens, Commented Dec 11, 2018 at 15:38

Dy.Lee · Accepted Answer · 2018-12-11 16:02:30Z

1

Try

Sub UploadData()

    Dim Model As Workbook
    Dim Q As Workbook
    Dim rngFX As Range
    Dim Year As String
    Dim Fn As String, wsName As String
    Dim strConn As String
    Dim strSQL As String
    Dim Ws As Worksheet
    Dim Rs As Object

    Set Model = ActiveWorkbook
    Set Ws = Model.Sheets("FOREX Forecast")

    Fn = Sheets("Instructions").Range("$C$29").Value
    'Set Q = Workbooks.Open(Filename:=Sheets("Instructions").Range("$C$29").Value)

    Month = "C" & Model.Sheets("Instructions").Range("$C$23")
    wsName = "[" & Month & " Summary" & "$" & "A61:R66 ]"


   strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & Fn & _
             ";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"

    Set Rs = CreateObject("ADODB.Recordset")

    strSQL = "select * from " & wsName

    Rs.Open strSQL, strConn

    Ws.Range("a3").CopyFromRecordset Rs

    Rs.Close
    Set Rs = Nothing

End Sub

edited Dec 11, 2018 at 16:02

answered Dec 11, 2018 at 15:08

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

eenz Over a year ago

I got an error in the Rs.Open line saying "Could not find installable ISAM"

eenz Over a year ago

I got a dialog box that said it could not find the worksheet. I think the problem is in the wsName line: the name of the file should be the Month and Summary, and A61:R66 is the range to be copied from this sheet - it should not be in the file name. How do I change this to be the range only?

Dy.Lee Over a year ago

fn is path & filename. wsName is [ Sheetname$a61:R66 ] . So wsName = "[" & "your sheet name " & "$" & "A61:R66 ]"

eenz Over a year ago

I've editted my post to show the error I'm getting - I'm not sure how to fix this

Dy.Lee Over a year ago

@Leena, What do you mean Month = "C" & Model.Sheets("Instructions").Range("$C$23")? This is related to the sheet name, and an error occurred because the correct sheet name was not given.

Collectives™ on Stack Overflow

VBA Excel code to retrieve data without opening a file

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related