PDA

View Full Version : How to transfer the data from other Workbooks to my worksheets in this case



steve_so
07-06-2007, 08:42 AM
AS TITLE~~

THANKyOU

lucas
07-06-2007, 08:45 AM
Do you wish for the userform to show when you open the excel file?

steve_so
07-06-2007, 08:47 AM
YES~~

lucas
07-06-2007, 08:54 AM
Put this code in the thisworkbook module in the visual basic editor....not a standard module:
Private Sub Workbook_Open()
UserForm1.Show
End Sub
userform1 is the name of your userform.

steve_so
07-06-2007, 08:59 AM
what is the difference between " thisworkbook module " and "standard module"~~~

thx

lucas
07-06-2007, 09:08 AM
Look in the project explorer on the left side of the visual basic editor for thisworkbook....double click on it and paste the code from above into the pane on the right...close and then reopen the workbook.

unmarkedhelicopter
07-06-2007, 09:08 AM
One relates to ThisWorkbook (in project window) and the other is attached to the project and can apply to worksheets, workbook or can be available across workbooks.

What does "~~~" mean ?

Bob Phillips
07-06-2007, 10:33 AM
It's growing, started as ~~, now is ~~~

steve_so
07-06-2007, 06:13 PM
I don't know how to write this vba for excel
Condition:
(1) Show the UserForm when i open the excel
(2) Click the button to have the multiple selection (this one had done)

Private Sub CommandButton_OpenFile_Click()
Dim Finfo As String
Dim FilterIndex As Integer
Dim Title As String
Dim Filename As Variant
Dim i As Integer
Dim Msg As String

' Setup lists of file filters
Finfo = "Comma separated Files (*.csv),*.csv," & "All Files (*.*),*.*"

' Display*.* by default
FiterIndex = 5

' Set the dialog box caption
Title = "select a File to Import"

' Get the filename
Filename = Application.GetOpenFilename(Finfo, FilterIndex, Title, MultiSelect:=True)

' Exit if dialog box canceled
If Not IsArray(Filename) Then
MsgBox "No file was selected."
Exit Sub
End If

'Display full path and name of the files
For i = LBound(Filename) To UBound(Filename)
Msg = Msg & Filename(i) & vbCrLf
Next i
MsgBox "You selected:" & vbCrLf & Msg
End Sub

(3) Show the selected excel sources in the listbox (in user form)
(4) Click the different button to choose the each parameter from these one or multiple excel.

Pls help ~~

lucas
07-06-2007, 06:54 PM
Steve,
Could you post your workbook. It would be easier to see what you're trying to do.

steve_so
07-08-2007, 07:31 PM
How to open the selected multiple file and show in the Listbox~~

THX

steve_so
07-09-2007, 01:53 AM
I don't know how to write this VBA in this condition
(1) Click the "Open" button to select the multiple csv files
http://img220.imageshack.us/my.php?image=41133901ee9.jpg
http://img169.imageshack.us/my.php?image=92031132uo6.jpg

(2) The selected files will show in the list box. When click the "Run" button, it will load the selected csv files. Or click the "Delete" button to delete the selected file(s).
http://img169.imageshack.us/my.php?image=56799144hn6.jpg

Thx for anyone help~~ It is very important for me~~

JimmyTheHand
07-09-2007, 02:29 AM
Hi

this might be what you need, except I didn't implement Delete button, because I didn't know what it was supposed to do. Delete from list, or delete from hard disk?

Jimmy

Bob Phillips
07-09-2007, 02:29 AM
Private Sub cmdOpen_Click()
Dim sFiles
Dim sFileShort As String
Dim i As Long

sFiles = Application.GetOpenFilename("CSV Files (*.xls), *.xls", , "Select a file to import", , True)
For i = LBound(sFiles) To UBound(sFiles)
sFileShort = Right(sFiles(i), Len(sFiles(i)) - InStrRev(sFiles(i), "\"))
With Me.ListBox1
.AddItem sFileShort
.List(.ListCount - 1, 1) = sFiles(i)
End With
Next i

End Sub

Private Sub cmdRun_Click()
Workbooks.Open Filename:=Me.ListBox1.List(Me.ListBox1.ListIndex)
End Sub

Private Sub cmdDelete_Click()
Me.ListBox1.RemoveItem (Me.ListBox1.ListIndex)
End Sub

steve_so
07-09-2007, 05:02 AM
To Jimmy

"Deleted means delete the csv files from lists

Thx~ FOR ALL

steve_so
07-10-2007, 01:32 AM
I have the question in the below problem:
(1) Since we can click the ?Open? button to select the csv file(s) into the ListBox1. This part VBA procedure had done and show below:
Private Sub cmdOpen_Click()
Dim sFiles As Variant
Dim sFileShort As String
Dim i As Long
Dim Title As String
Dim Finfo As String
Dim Msg As String

' Setup lists of file filters
Finfo = "Comma separated Files (*.csv),*.csv," & "All Files (*.*),*.*"

' Set the dialog box caption
Title = "select a File to Import"

sFiles = Application.GetOpenFilename(Finfo, , Title, MultiSelect:=True)

' Exit if dialog box canceled
If Not IsArray(sFiles) Then
MsgBox "No file was selected."
Exit Sub
End If
'Display full path and name of the files
For i = LBound(sFiles) To UBound(sFiles)
sFileShort = Right(sFiles(i), Len(sFiles(i)) - InStrRev(sFiles(i), "\"))
With Me.ListBox1
.AddItem sFileShort
.List(.ListCount - 1, 1) = sFiles(i)
End With
Msg = Msg & sFiles(i) & vbCrLf
Next i
MsgBox "You selected:" & vbCrLf & Msg

End Sub
http://img261.imageshack.us/my.php?image=11hs3.jpg
(2) Also, I set the string in the listbox2.
Private Sub UserForm_Initialize()
With ListBox2
.AddItem "115 Vac 60 Hz"
.AddItem "129 Vac 60 Hz"
.AddItem "117 Vac 60 Hz"
.AddItem "96 Vac 60 Hz"
End With
Worksheets("Sheet1").Activate
End Sub
(3) In the selected csv file, the content is around like this graph. Then, I want to choose the integers in this 3 cells and take a means of this 3 integers to transfer my worksheet by vba setting
http://img261.imageshack.us/my.php?image=12tz0.jpg

Now, my action is : when I selected the csv file in list box and select the string in the listbox2, then I click the ?Analyze? button to transfer the integers from the selected workbooks to my worksheets
http://img261.imageshack.us/my.php?image=13we0.jpg

Thx for ALL helping~~

steve_so
07-10-2007, 08:05 AM
Anyone can help me~~ It is very important for me~~ THX~

lucas
07-10-2007, 09:09 AM
Threads merged....Steve, try to keep your posts about one question in one thread so everyone will have the background to help with your question. It would also help if you get a solution to the thread if you marked it solved using the thread tools at the top of the page.

steve_so
07-10-2007, 06:54 PM
OK~~ I will follow it~~~

Also, i have the question :Can VBA support the cells value from the workbooks (not open status) transfer to the worksheets~~THX