Open different workbook and store data to variables

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!

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

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.

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.

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

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

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'

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

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

Date_Time_Table.xls

Hi I have attached the workbook.

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.

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.

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

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?

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