Open different workbook and store data to variables

[Cerkit]

Hi. I am fairly new to Visual Basic. I am trying to write code which will do the following

- Prompt user to browse and select the file they want data from
- Copy data from particular cells and store it in variables

I have made a start as shown below

Sub Prompt() 'Prompt user to select file from which to read data

Ref_Wbook = Application.GetOpenFilename("TextFiles (*.xls), *.xls") 'Command to allow browse procedure

End Sub


I guess I do not know how to address the data workbook so that I can refer to it, saying ok now that user has selected I now want to work in a 'Particular' sheet in that workbook.

Any help would be appreciated.

Thanks!

[Norie]

You need to open the workbook first.

Sub Prompt() 'Prompt user to select file from which to read data
Dim Ref_WBook As String

   Ref_WBook = Application.GetOpenFilename("Excel File (*.xls), *.xls") 'Command to allow browse procedure

   If Ref_WBook <> "False" Then
        Workbooks.Open Ref_WBook
   End If
End Sub

Not sure what you want to do next.

[Cerkit]

Hi. Thanks for your help. What exactly is going on in the following line?

Ref_WBook <> "False"

After opening the file I want the macro to specify a particular sheet in that workbook to work in, and copy everything underneath a particular cell location, say everything underneath "A1" into my current workbook.

From what I have read I understand that I need to make the particular sheet I want copy from to be the active sheet so something along the lines of

Data_sheet = Application.Activesheet

as I don't necessarily know how many rows will have data in the column I will put a loop which will scan the column and find out at what point the column becomes null, that will indicate how many rows in that column it should copy data as I will put a counter. Once I know then I just want to have that data pasted in a column underneath the cell "B1" in my current workbook.

I guess the first thing I need advice on is how to specify which sheet in this opened workbook I want to work in.

Then how do I reference this sheet, so if I want to specify certain cells in this sheet will it be a case of saying for example if I want to store the data in the first three columns on row A would the following w

Variable = Workbook(Ref_Wbook). Sheets(Data).Range(A1:A3)

Thanks

[Norie]

That line of code checks to see if the user has clicked Cancel in the dialog.

You don't say which worksheet you want to copy from so I'll assume it's the active sheet in the workbook the code is in.

Sub Prompt() 'Prompt user to select file from which to read data
Dim wbSrc As Workbook
Dim rngSrc As Range
Dim rngDst As Range
Dim Ref_WBook As String

   Ref_WBook = Application.GetOpenFilename("Excel File (*.xls), *.xls") 'Command to allow browse procedure

   If Ref_WBook <> "False" Then
        Set wbSrc = Workbooks.Open (Ref_WBook)

        With wbSrc.Worksheets("Data")
            Set rngSrc = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
        End With

 
        Set rngDst = ThisWorkbook.ActiveSheet.Range("B1")

        rngSrc.Copy rngDst 
   End If
End Sub

[Cerkit]

Hi. Thank you so much for your help. The worksheet I want to copy from is in a different workbook than the one the code is in. The code is in the workbook I want to copy to, which is my main workbook I guess.
Whereas Ref_Wbook is just the one with the data in it.

Thanks again.

[Norie]

So there are three workbooks involved?

[Cerkit]

No apologies if I have communicated wrongly. There are two workboooks. One is my main workbook where I am doing my actual work, this contains the macro in it and the job of the macro is to bring in the relevant data from the Data sheet in the Ref_Wbook.

[Norie]

So the code I posted should work, ThisWorkbook refers to the workbook the code is in.

The only thing you might want to change is ActiveSheet to make sure the data goes to the right worksheet.

[Cerkit]

It works. You are a legend thank you so much!

[Cerkit]

Hi. I understand what the following line is for but can you just elaborate a bit, just need to understand it a bit more as I may need to change it

Range("A1", .Range("A" & Rows.Count).End(xlUp))

If I need it to stop when it encounters a particular piece of text for example 'End' and copy everything up to but not including the 'End'.

Thanks

[Norie]

That piece of code sets the range to copy.

"A1" is the start of the range.

.Range("A" & Rows.Count).End(xlUp finds the last row of data in column A by going to the bottom of the column and moving upwards, just as you would do it manually with the End key.

We combine these to get a reference to the range we want to copy.

If you only want to copy from A1 down to the first occurrence of 'End' you could try something like this.

FirstEndRow = Application.Match("End",.Range("A:A"), 0)
Set rngSrc = .Range("A1:A" &FirstEndRow)

That's untested, it should work though.

[Cerkit]

I set it As String and it works. Is that correct procedure?

[Cerkit]

Hi. What kind of variable must I set the the FirstEndRow to be, I tried it as Range but it says Error 'Object or With statement Variable not defined'

[Norie]

It should really be declared as Variant.

That's to take care of 'End' not being found.

If that happens Application.Match will return an error.

Actually a check for that should probably be added, because if there is an error it'll cause problems later in the code.

[Cerkit]

Hi. Me again.

I am trying to write a code for which I have two pieces of information, two variables X and Y. I want to use these variables in the style of Cartesian coordinates to locate a value in a table in a sheet in another workbook. So basically in this table I have dates in my A column and times in my first row with the dates range for 365days and the times in half hour periods so using 48 columns. So essentially given a particular date and time I can locate the value in the table corresponding to those X Y values.

My idea is to do a comparison so start of by saying

1 Compare Y with every cell in the Range(A1:A365)

1.1 If it is equal then take note of the count in order to remember the row number and store as ROW

2 Compare X with every cell in the Range (A1: X1)

2.1 If it is equal then take note of the count in order to remember the column and store as COLUMN

Then I can use those variables to obtain a value for any given X,Y and locate the cell COLUMN, ROW.

Does this sound like an efficient way to do this or is there a better way?

Thanks

[Norie]

There might be a better way, hard to tell without seeing an example of the data and how it's structured.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Cerkit]

Date_Time_Table.xls

Hi I have attached the workbook.

[Cerkit]

The time row at the top would go on to complete 24 hours.

So basically with this workbook if someone gave me a date 05 - Aug -2012 and a time interval 02:00 - 02:30 I want to be able to retrieve the value in the cell which corresponds to that date and time and copy it into my active workbook.

[Cerkit]

Hi. Just another thing when browsing for a file as you showed me earlier, how can I specify so that it already takes the user to a folder where the file to be selected is found to make it easier for the user to locate and open?

Thanks again.

[Norie]

You can use ChDir to change the current directory to the one you want.

If you need to change drive then you can use ChDrive.

By the way, I had a look at the workbook but I was wondering where the user input was coming from.

Would there be a userform involved?

Actually, will there be any user input?

[Cerkit]

No. The only thing the user will do is run the macro and locate the file. Then the macro obtains the Date and Time from ThisWorkbook and performs a search on the source workbook matrix to locate the cell coresponding to the particular date and time.

[Norie]

So this is linked to the original thread and the input, ie date and time, will come from the workbook the code is in?

[Cerkit]

Yes this is linked to initial thread. The first part that you showed me allowed me to acquire data from a workbook and that included the date and time values. Now this new macro will utilise that data, which now resides in ThisWorkbook, to perform the search on the another workbook which is the date - time matrix workbook which I posted.

Thanks

[Cerkit]

Hi. I have managed to write something that works. Is there a way that I can open several files in a folder in turn, perform the routine on them in order to extract a value from each one?

[Cerkit]

Hi. I have written the following subroutine that I want to perform on several workbooks. This routine basically locates the same cell in the same sheet in each workbook and retrieves the value and stores it in my current workbook from which the code is running.

I need to write a code so that it will have a loop, first to account for opening all the workbooks in a given folder but also because the the row number to which I want to store the retrieved value to needs to increase each time so that the next value obtained from the next workbook is stored below the previous and so forth, I have labelled this in the code as "Needs_to_Loop".

Any help would be much appreciated! Thanks

My routine is as follows

Sub Acquire_Values()

Dim Source_WBook As Workbook 'Declare Workbook variable
Dim Ref_WBook As String 'Reference variable for workbook
Dim Date_Index As Date 'Date Variable from ThisWorkbook
Dim Time_Index As Variant 'Time Variable from ThisWorkbook
Dim Row_Index As Variant 'Variable source row number
Dim Column_Index As Variant 'Variable source column number

Date_Index = ThisWorkbook.Worksheets("Sheet1").Range("C1").Value 'Get Date from this Workbook

Time_Index = ThisWorkbook.Worksheets("Sheet1").Range("C2").Value 'Get Time from this Workbook

Ref_WBook = Application.GetOpenFilename("Excel File (*.xls), *.xls ") 'Command to allow browse procedure

If Ref_WBook <> "False" Then 'Check if file selected, if not then script is cancelled
Set Source_WBook = Workbooks.Open(Ref_WBook) 'Open the workbook

With Source_WBook.Worksheets("Sheet1") 'Perform following process focused on Source_Workbook

Row_Index = Application.Match(CLng(Date_Index), Range("K:K"), 0) 'Find the Row number for the given Date

Column_Index = (Time_Index + 12) 'Shift the column number because the times start at column M

ThisWorkbook.Worksheets("Sheet1").Range("Needs_to_Loop").Value = Source_WBook.Worksheets("Sheet1").Cells(Row_Index, Column_Index).Value

End With

End If

End Sub