select workbook

[barbar]

Hi, I got an issue I had not found answer for.

I have a tool, that makes calculations. I would like to import data to that tool from files, instead of typing them in every time.

The problem I got: how to allow user of a tool to select folder in which file needed is selected and then a file from which data should be taken?

The folder and name of the file change every time so it has to be user specified. Ideally also chance to choose a sheet to be imported from selected workbook.

the tool is intended as multiuser one, so creating a special forlder and assigning one specific name would be a bit too cumbersome for this one.

can anyone help???

[CreganTur]

The problem I got: how to allow user of a tool to select folder in which file needed is selected and then a file from which data should be taken?


Are you pulling data from an Excel spreadsheet, or from a different file type?

Does the data need to be imported into a table in your database so that your tool can get to it, or does something else need to be done with the data?

The more detailed you can be the better quality of answer you'll get.

[barbar]

Hi, thank you for prompt response.

Think i should have posted it in excel thread.

Yes, I need to take data from excel spreadsheet (possibly closed) to another tool made in excel.

Basically excel based tool makes calculations and than produces 'print out' with all parameters of calculation (spreaded all around, to make easy human read, not computer one). Than print out can be saved by user (anywhere and under any name)

I would like to that tool (in excel) to 'read back' the information from 'print out' in case calculation needs to be repeated.

my code goes sth along lines:


Sub aimport()
Dim a, b As String
Dim keep_data As Long
Dim cell As Range
a = "calculation.xls"
b = "Print out"

ThisWorkbook.Worksheets("setup").Range("ID").Value = Workbooks(a).Worksheets(b).Range("E200").Value

ThisWorkbook.Worksheets("sheet1").Range("data").Value = Workbooks(a).Worksheets(b).Range("E9").Value

ThisWorkbook.Worksheets("sheet1").Range("amount").Value = Workbooks(a).Worksheets(b).Range("E201").Value

ThisWorkbook.Worksheets("sheet2").Range("U1").Value = Workbooks(a).Worksheets(b).Range("E203").Value

ThisWorkbook.Worksheets("sheet3").Range("AJ2").Value = Workbooks(a).Worksheets(b).Range("E205").Value

ThisWorkbook.Worksheets("sheet3").Range("V11").Value = False

ThisWorkbook.Worksheets("input").Range("V28").Value = Workbooks(a).Worksheets(b).Range("E230").Value

If ThisWorkbook.Worksheets("input").Range("I7").Value <> Workbooks(a).Worksheets(b).Range("K17").Value Then
MsgBox ("check values")
Else
End If

and so on....

This maybe is not the most effective but works.

My problem is how to enable user to select each time folder and workbook (and than possibly sheets) that contains data. User form? can create it, but i do not know how to make procedure that enables selection of the folder. in the procedunre workbook is shown as a, and no indication of path of the folder, as this is part i have trouble with.

Ideally it should import data from closed workbook.

Any ideas?

thanks a lot!

Barb

[CreganTur]

You can use a File Dialog window to allow your users to select the file that they want to work with. You need to make sure that you have set a reference to a Microsoft Office Object Library (you may have 11.0 or 12.0).

This code opens a File Dialog window that will allow your users to select multiple excel spreadsheets to work on. You can restrict them to only 1 spreadsheet at a time by changing .AllowMultiSelect = True to .AllowMultiSelect = False.



Dim strFilepath As String
Dim strSheetName As String
Dim instrFile As String
Dim instrItem As String
Dim dlgOpen As Office.FileDialog
Dim vrtSelectedItem As Variant
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen

.AllowMultiSelect = True '<<<User can select multiple files | False for single file
.Title = "Please select file to load" '<<<Title text for window
.Filters.Clear '<<<Removes any old File Dialog filters
.Filters.Add "Excel Files", "*.XLS" '<<<sets filter to Excel files

If .Show = 0 Then '<<< if User presses Cancel then Sub ends
Exit Sub
Else
'Will loop through all files selected by the Dialog window
For Each vrtSelectedItem In .SelectedItems
strFilepath = vrtSelectedItem '<<<Set filepath to Variable
'Insert your procedures or calls here
Next vrtSelectedItem
End If
End With

I hope this can at least give you a starting point for your project.

[barbar]

Hi,
thank you for response :bow:
looks like this is what I was looking for :yes
but now have to think through this a bit more to tell for sure that it works, and code gave me a bit of thinking for now.
:think: .
I'll post reply when i'll make if working.
thanks a mill.