PDA

View Full Version : [SOLVED] VBA Help.....where to start?



cmefly
01-17-2005, 09:55 AM
Hi,

here is my situation. I've got this huge master customer list....maybe not that huge....150 rows.....50 columns (ID#, customer name, number, balance, etc....)

Now, each customer has there own excel file..and i must note that all customer files are identical - just different info.

My job is to populate the master file from the customer files....

This is what i've thought off.....have a macro that asks the user what's the exact name of the customer file (which is already open)....then have the macro populate the row that has the same ID# as the one located in the customer file.

is this possible? this is what i've got so far....pathetic...but it's a start


Sub extractor()
sheetname = InputBox("Enter the EXACT File Name of the customer file" & _
" you wish to extracted data from; Please make sure the file is already open")
end sub


thanks,

Marc

surya prakash
01-17-2005, 10:07 AM
try this post

http://www.vbaexpress.com/forum/showthread.php?t=1519

cheers/ prakash

Zack Barresse
01-17-2005, 10:09 AM
Hey Marc,

Couple of suggestions.

1) Stay away from InputBox's when asking users for filenames, user names, ID's, ets. It opens the door for way too many mistakes on the user's end. If possible, create a small userform to populate itself upon opening with all of the items that YOU want it to. That way you know the responses that you will receive and don't have to anticipate every bonehead move a user may make.

2) Use the GetSaveAsFilename and GetOpenFilename for open/closed file names. I find they are much easier to deal with on the 'back-end' (coding). You can use these (i.e. FileFilter) to narrow down what you allow your user's to choose and navigate them where you want them to go.

Also, when searching for something specific, I suggest using the Find method. It's fast, and if used properly can save you a lot of headache's. :yes


If you'd like some help with this, can you post an example sheet that contains your user data? If sensitive data, strip out all unecessary data, leave with 'placebo'-type data. Also, will the file always be open? How will you know?

cmefly
01-17-2005, 10:11 AM
try this post

http://www.vbaexpress.com/forum/showthread.php?t=1519

cheers/ prakash
Prakash,

thanks for the reply but i can't follow the code.....

help! lol...

marc

cmefly
01-17-2005, 10:17 AM
Also, will the file always be open? How will you know?
Hi Zack,

Good to hear from you. Actually, i'm the only one that will be using this master list.....so basically, i've got a hundred customer files.....all consisting of the same template...and i just need a master document so i don't have to go searching through each one....

i'm not sure how to attach my file so this is basically what it looks like...

Headings right across the top: Customer_ID, First name, Last Name, Phone Number, Account Number, Balance, etc.

First Column (A): Customer ID numbers (found in Sheet1, cell b5, of the customer file)

any ideas?

thanks,

Marc

surya prakash
01-17-2005, 10:19 AM
hi marc
can you attach your sample files, so that someone can have a look and help you out

Zack Barresse
01-17-2005, 10:25 AM
Hmm. Well a few questions come to mind.

What are these files called?
Are they named in a specific order?
Do you have the file name associated with each ID number/name?
What do you want to trigger this macro? (Button, sheet event, etc.)
What exactly is being brought back from these customer sheets?


As far as attaching the file. It must be zipped. If using Windows XP, right click the file --> Send To --> Compressed Folder. Then by clicking the 'Post Reply' or 'Go Advanced' you will be taken to the post page. From there, scroll down until you see the button 'Manage Attachments'. Click it. Click the 'Browse' button, click the file, click Ok. Be sure to click the 'Upload' button after that. Give it a few seconds and you should see your filename pop up there. There is a limit of about 244 Kb (or something like that).

cmefly
01-17-2005, 10:41 AM
Hmm. Well a few questions come to mind.

What are these files called?
Are they named in a specific order?
Do you have the file name associated with each ID number/name?
What do you want to trigger this macro? (Button, sheet event, etc.)
What exactly is being brought back from these customer sheets?


As far as attaching the file. It must be zipped. If using Windows XP, right click the file --> Send To --> Compressed Folder. Then by clicking the 'Post Reply' or 'Go Advanced' you will be taken to the post page. From there, scroll down until you see the button 'Manage Attachments'. Click it. Click the 'Browse' button, click the file, click Ok. Be sure to click the 'Upload' button after that. Give it a few seconds and you should see your filename pop up there. There is a limit of about 244 Kb (or something like that).
hi guys,

here you go....

to answer some questions...i'd like to press a button on the master file to extract the info....all files are called something different...for reasons above and beyond me...lol...
within each file, there is a customer ID# that I have listed in the master copy.

hope this helps,

thanks for taking your time,

Marc

Zack Barresse
01-17-2005, 10:48 AM
Okay, then we need to work on what links these file (names) to the ID numbers in your master workbook. It would be rather unefficient to loop through all files in a folder and check a cell for a matching number. Can you give examples of the names of the workbooks? Do they contain names? Names found in your master workbook?

cmefly
01-17-2005, 10:51 AM
Okay, then we need to work on what links these file (names) to the ID numbers in your master workbook. It would be rather unefficient to loop through all files in a folder and check a cell for a matching number. Can you give examples of the names of the workbooks? Do they contain names? Names found in your master workbook?
hmmm,...

zach, i'd rather have it that as such...
both files open (master and customer1)
press button on master to extract customer info.....

i'd rather do this b/c the files are located throughout our complicated network system......

cmefly

Zack Barresse
01-17-2005, 11:01 AM
Well, if those two files will be the only ones open, you may be able to use something like this ...


Option Explicit

Sub GetCustomerInfoPlease()
Dim moreOpen As Boolean, masterWb As Workbook, masterWs As Worksheet
Dim i As Long, cnt As Long
moreOpen = False
Set masterWb = ThisWorkbook
Set masterWs = masterWb.Sheets("Sheet1") 'Change if necessary
cnt = 0
For i = 1 To Application.Workbooks.Count Step 1
If Windows(i).Visible Then
cnt = cnt + 1
End If
Next i
If cnt > 2 Then moreOpen = True
If moreOpen Then
MsgBox "You have more than two workbooks open!"
Else
MsgBox "We're good to go!"
End If
End Sub

Are you just looking to bring back the information on that first sheet in your customer file?

cmefly
01-17-2005, 11:04 AM
Well, if those two files will be the only ones open, you may be able to use something like this ...


Option Explicit

Sub GetCustomerInfoPlease()
Dim moreOpen As Boolean, masterWb As Workbook, masterWs As Worksheet
Dim i As Long, cnt As Long
moreOpen = False
Set masterWb = ThisWorkbook
Set masterWs = masterWb.Sheets("Sheet1") 'Change if necessary
cnt = 0
For i = 1 To Application.Workbooks.Count Step 1
If Windows(i).Visible Then
cnt = cnt + 1
End If
Next i
If cnt > 2 Then moreOpen = True
If moreOpen Then
MsgBox "You have more than two workbooks open!"
Else
MsgBox "We're good to go!"
End If
End Sub

Are you just looking to bring back the information on that first sheet in your customer file?
Hey,

I'm looking to bring back the information on the first and second sheet and place it into the master list in the row containing the correct customer ID

cmefly
01-17-2005, 11:25 AM
Firefytr,

your code is only a check...how do i go about extracting?

sorry for the hassle i'm putting you through today...

thanks ,

marc

Zack Barresse
01-17-2005, 11:37 AM
No hassle at all Marc. :)

Question: What cell will the phone number be entered into on the target workbook? Working on a userform example for you.

cmefly
01-17-2005, 12:14 PM
No hassle at all Marc. :)

Question: What cell will the phone number be entered into on the target workbook? Working on a userform example for you. wow..thank you...

it's actually not needed... :)

thanks

marc

Zack Barresse
01-17-2005, 12:20 PM
Okay, this is what I have come up with so far. Tested slightly. This is all done inside the master workbook.

In a Standard Module:


Option Explicit

Sub CallUserFormPlease()
Load UserForm1
UserForm1.Show
End Sub

Create a UserForm with 1 Combobox, 1 TextBox, 3 Command Buttons. ComboBox1 = ID number, TextBox1 = TargetFile name (check), CommandButton1 = Confirm, CommandButton2 = Cancel, CommandButton3 = Change.

Add this code to the UserForm:


Option Explicit

Private Sub CommandButton1_Click()
Dim masterWb As Workbook, masterWs As Worksheet
Dim targetWb As Workbook, lastCol As Long, rngStep As Range
Dim i As Long, cnt As Long, cel As Range, n As Long
Set masterWb = ThisWorkbook
Set masterWs = masterWb.Sheets("Sheet1") 'Change if necessary
cnt = 0
'myVal = Me.ComboBox1.Value
For i = 1 To Application.Workbooks.Count Step 1
If Windows(i).Visible = True Then
cnt = cnt + 1
End If
Next i
Select Case cnt
Case 1
MsgBox "You only have the master workbook open!" & vbCrLf & _
"Please open the target workbook", vbInformation, "ERROR"
GoTo EndMe
Case 2 'your target
Set targetWb = Workbooks(Me.TextBox1.Value)
If Me.ComboBox1.Value <> targetWb.Sheets("info").Range("B4").Value Then
MsgBox "Your ID number chosen and the target" & vbCrLf & _
"workbook ID number do not match!", vbInformation, "ERROR"
GoTo EndMe
End If
'info sheet
Set rngStep = targetWb.Sheets("info").Range("B4,B6,B8,B10,B12,B14,B16,B18,B20,B22,B24,B26")
n = 3
For Each cel In rngStep
masterWs.Cells(Me.ComboBox1.ListIndex + 2, n).Value = _
cel.Value
If cel.Row = 24 Then
masterWs.Cells(Me.ComboBox1.ListIndex + 2, n).Value = _
cel.Value & cel.Offset(, 2).Value
End If
n = n + 1
Next cel
'accounting info sheet
lastCol = masterWs.Range("IV1").End(xlToLeft).Column
For i = 15 To lastCol Step 1
masterWs.Cells(Me.ComboBox1.ListIndex + 2, i).Value = _
targetWb.Sheets("accounting info").Cells(2, i - 13).Value
Next i
masterWs.Cells.EntireColumn.AutoFit
Case Else
MsgBox "You have more than two workbooks open!" & vbCrLf & vbCrLf & _
"Please ensure the master and only one target" & vbCrLf & _
"workbooks are open.", vbInformation, "ERROR"
GoTo EndMe
End Select
EndMe:
Unload Me
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton3_Click()
Dim fName As String, i As Long
fName = Application.GetOpenFilename("Excel Target Files (*.xls), *.xls")
If fName <> False Then
For i = Len(fName) To 1 Step -1
If Mid(fName, i, 1) = "\" Then Exit For
Next i
Me.TextBox1.Value = Right(fName, Len(fName) - i)
End If
End Sub

Private Sub UserForm_Initialize()
Dim i As Long, lastRow As Long, IDsheet As Worksheet
Dim thisWb As Workbook, wb As Workbook
Set IDsheet = ThisWorkbook.Sheets("Sheet1") 'change if necessary
Set thisWb = ThisWorkbook
lastRow = IDsheet.Range("B65536").End(xlUp).Row
With Me.ComboBox1
For i = 2 To lastRow Step 1
.AddItem IDsheet.Range("B" & i).Value
Next i
.ListIndex = 0
End With
For Each wb In Application.Workbooks
If Windows(wb.Name).Visible Then
If wb.Name <> thisWb.Name Then
Me.TextBox1.Value = wb.Name
End If
End If
Next wb
End Sub

Add a button to your master sheet and assign it 'master copy.xls'!CallUserFormPlease

I'll attach the master I tested with. HTH


Edit: One line of code changed. Find this line ...


cel.Value & cel.Offset(, 1).Value
..change to

cel.Value & cel.Offset(, 2).Value
Changed in post, not on attachment.

cmefly
01-17-2005, 12:25 PM
Wow..!! give me some time to give it a shot...

in the meanwhile, i've come up with this....

why doesn't it work?


Sub Macro4()
sheetname = InputBox("Enter the EXACT File Name")
ActiveCell.FormulaR1C1 = _
"=INDIRECT(""'"" & sheetname & ""sheet1"" & ""'!"" & ""d22"")"
End Sub

cmefly

Jacob Hilderbrand
01-17-2005, 02:00 PM
Don't double up on the quotes in your formula.


ActiveCell.FormulaR1C1 = _
"=INDIRECT(""'"" & sheetname & ""sheet1"" & ""'!"" & ""d22"")"

Try this instead:


ActiveCell.Value = "=[" & sheetname & "]sheet1!d22"