Posts by mycomputerguy-w

I think i got this all sorted. Ill let you know if i still need help.

Thanks!

Royuk, or Mumps.... could either of you look into this. I've been racking my brain trying to find a solution and i haven't been successful. the if and statement will only compare the first argument.

Thanks in advance guys!

I have also figured out the concatenate procedure so I'm good on that issue.

one other issue is, once the import completes, I need to concatenate columns B and C with a space between. This is a new thing for me so I have no idea how to even begin to form that part. Its easy to do as a formula but ive never used it in VBA format before.

After moving this code into the main app, I did find a couple issues I'll start with this one simply because by solving this, it may very well solve the rest of the issues. The main app uses a table with headers and the import sub you created, adds everything below the actual table. I'd like this to append the data into that table preserving the headers but adding dynamically to the table, either at the bottom of top. because this table is sorted, once refreshed, it should all be back in alphabetical order.

comment out the line :

On Error GoTo err_exit

by using a ' in front of it. Run the code and tell us what the error is and which line errors out.

sorry, ive gotten side tracked on some other projects and Yes, i will work on this and see about getting you a solution soon.

I actually got that sorted on my own Mumps..... Thanks for all the help.

see the updated code below.

Sub CopyColumns()
Dim lastrow As Long, erow As Long, Rng As Range, ws As Worksheet, wb As Workbook
Dim filter As String, targetWorkbook As Workbook, Ret As Variant
Dim wsCopy As Worksheet, wsDest As Worksheet, CopyRow As Long, DestRow As Long

 Application.ScreenUpdating = False
 Dim flder As FileDialog, FileName As String, FileChosen As Integer, wkbSource As Workbook
 Dim RngList As Object, desWS As Worksheet, srcWS As Worksheet, Val As String, arr1 As Variant, arr2 As Variant
 Set desWS = ThisWorkbook.Sheets("Voter Names")
 arr1 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
 Set flder = Application.FileDialog(msoFileDialogFilePicker)
 flder.Title = "Please Select a folder and file."
 FileChosen = flder.Show
 FileName = flder.SelectedItems(1)
 Set wkbSource = Workbooks.Open(FileName)
 Set srcWS = Sheets(1)
 
 
 With wkbSource.Sheets(1)
 .Range("I:N").Delete
 .Range("G:G").Delete
 .Range("A:C").Delete
 .Range("D:D").Select
  Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False
 Range(Range("F1"), Range("F1").End(xlDown)).Select
Set Rng = Selection
For Each cell In Rng
cell.Value = Trim(cell)
Next cell
Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False

 
 End With
 MsgBox "This could take a few minuets! Please be patient!"
 
 arr2 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
 Set RngList = CreateObject("Scripting.Dictionary")
 For i = LBound(arr1) To UBound(arr1, 1)
 Val = arr1(i, 1) & "|" & arr1(i, 2)
 If Not RngList.Exists(Val) Then
 RngList.Add Val, Nothing
 End If
 Next i
 For i = LBound(arr2) To UBound(arr2, 1)
 Val = arr2(i, 1) & "|" & arr2(i, 2)
 If Not RngList.Exists(Val) Then
 srcWS.Rows(i).EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
 End If
 Next i
 wkbSource.Close False
 Application.ScreenUpdating = True
End Sub

Im wondering if this line may be the problem. if both systems don't have the same exact environment, this line will most likely break the procedure:

S = Environ("appdata") & "\Microsoft\Signatures\"

I had to edit this line: "this should leave only columns A-C and column D on the sample I sent you." to read: "this should leave only columns A-C and column E on the sample I sent you"

ok.... couple things. first, this is doing exactly what i wanted it to do in regards to importing. HOWEVER, the sheet(s) that sent you are missing some data that will be in the original import files that are used. i purposely left this data and columns off of the sheets I sent due to its sensitive nature. let me see if i can explain this a little better. The file that is selected, needs to have columns A-C deleted (the whole column(s)), Column G Deleted and columns I-N deleted. this should leave only columns A-C and column E on the sample I sent you. Boy that really sounds confusing when I read it. I hope that makes sense to you.

All of these removed columns in the original file as mentioned above, will have to be deleted before the compare is done. I didn't think about this until I was testing the import and it drug in ALL this redacted info. after the import is complete then i will be running a separate sub to clean and normalize the data and use text to columns to put it all into the correct format. which by the way, there WILL be headers (Column Names).

hopefully this makes sense.

just the first and last names would be fine. that should be enough definition to determine if it is a duplicate.

ok... after reading it again, that is a little confusing. what i was thinking is to check the file that is sent to see if there are multiple sheets and if those multiple sheets ALL had data but lets leave that part out. ill train the users to prep the file before import. lets just assume that the import file will have a single sheet and that data WILL have duplicates. ill send new samples with data in each. if we could import the data from book2 to importtest, that is what ill need to accomplish. Ive loaded both files with data, some of the data is duplicated. (towards the bottom)

ok.... here we go,

first- the file name will most likely never be the same name or be in the same folder nor will the sheet names be the same. The data will be coming from the same place but it is never built or sent out by the same person every time so the chances of anything being the same are pretty slim. So to answer your question, yes, the file picker dialogue pop up is the best option that I can think of.

2- explaining in detail, the quote 'After the file is open, I need to get the sheet name(S) from that file" I guess I really don't NEED the sheet name but my thought was that the sheet name or location of the sheet in the workbook could change breaking the procedure because, as explained above, the chances of the file names or the sheet names being the same is slim to none. So once the file being imported is open, I wanted to first check to see how many sheets there are, is there data in ALL sheets, if there is more than 1, and is that data the same or different in each sheet , if the data is ONLY in one sheet, even though there might be a single or multiple sheets, then, where ever the data is, compare the data in THAT sheet to the current data in the other workbook (which, right now is named importtest.xlsm and could change) . If ALL the data is the same in both places ( which it could be) then just quit the sub. If the data has different entries, than select JUST the different entries and append them to the bottom of the sheet "Voter Names" in importtest.xlsm.

3rd- when I wrote this post, my idea was to copy all data , AFTER it was formatted with the TextToColumns call, into a second sheet on importtest.xlsm. I've since refined my thoughts and figured out, that would be an extra and unnecessary step in what is already turning out to be a larger procedure than originally anticipated. Comparing the data in the the 2 sheets would be the better option if possible.

Let me take a minute and explain to you what this "App" that I'm building is for and what it is anticipated to do.

This import procedure is one cog in a very much larger App. The idea here is to build an all in one solution for towns and cities under the jurisdiction of a larger county seat. each "Town" or "City" is responsible for its citizens on the voter roll. if that citizen decides that they want to vote by absentee ballot, there is a huge procedure that needs to be performed by the city or town officials all while complying with local ordinances. The app holds all the voter information. when people leave the area, come to the area, die, are born, become voter age and register, don't register and don't vote for a few years then decide they want to later.... these are all reasons that they could be removed or added to the data I'm trying to import. Those are the entries I'm looking for. The data is built by employees in the COUNTY government, and can be sent to the Towns or Cities at their request. their particular data will be specific to them and their township. This is why the button exists. Its on the settings page of the userforms that aren't included in this sample simply because the sample is getting too big to send so I have to break it apart and work on bits at a time. That button will be for them to update their "Database" of names. Because the data comes from the county seat, it most likely is going to change and that presents the problems I'm attempting to address here.

I hope this helps you see my vision. As always, im open to suggestions and always welcome changes that will make the code and the app run better and be more efficient. Any questions, feel free to ask.

Cheers!

OK Gurus, I have another problem for you. I need a sub routine that will allow for a customer file to be opened from a command button (at the moment, I have it set up with the Application.GetOpenFilename method with now command button , just using the run command). If this is the most efficient way, than I will use that. After the file is open, I need to get the sheet name(S) from that file. (data should most always be on the first sheet. Fields and data will look like the example in book2). After getting the sheet names, I then need to copy the whole sheet to sheet TWO of the example "ImportTest.xlsm" . After getting it to sheet 2, the formatting takes off but I've got that working so unless you see a better way to do it, that doesn't need any help. Then I need to compare sheet2 with existing data on sheet1 to be sure there is no duplicates, delete any duplicates, copy THAT data and append it AT THE BOTTOM of the data on sheet one that will already be there. whew!! i think i explained it right. I'll start there and see what kind of replies I get. as always, Any questions, feel free to ask, ill do my best to answer them.

Thanks for your great work!!

You are right!! My apologies! You are in the General Forum. Not sure why this popped up in the VBA forum room.

Have a great day!

If you aren't using VBA to do this, then I think you may be in the wrong forum. If what you are looking for is to have just a "Third Column" with the percentage result, that would be a question for the "Formulas" forum. the column would just be exactly that, another column. then the percentage formula would be added to the cell for calculation.

I would at least try to post your question there and see anyone can help you with it.

Have a good day! sorry i couldn't be more help!

Thanks Roy... With your example, it got me on the right path. Its working perfect now.

The old code:

Private Sub cmbtnPrint_Click()
 Dim otable As Range, r As Long, c As Long, sAddr As String, rAddr As String
 Dim oWord As Word.Application, oDoc As Word.Document

        With Me.ListBox1
            If .ListIndex = -1 Then Exit Sub '''/// no selection made
                ''///check if the current value is selected
            For iX = 1 To .ListCount
                ''///check if the current value is selected
                If .Selected(iX - 1) = True Then
                    sAddr = .List(iX - 1, 1) & " " & .List(iX - 1, 0) & vbCr & .List(iX - 1, 2) & vbCr & .List(iX - 1, 3) & " " & .List(iX - 1, 4) & " " & .List(iX - 1, 5)
                    rAddr = Sheets("Data").Range("A1").Value & vbCr & Sheets("Data").Range("A2").Value & vbCr & Sheets("Data").Range("A3").Value & vbCr & Sheets("Data").Range("A4").Value & ", " & Sheets("Data").Range("A5").Value & ", " & Sheets("Data").Range("A6").Value
                End If
            Next iX
        End With
            Set oWord = CreateObject("Word.Application")
            Set oDoc = oWord.Documents.Add

                If Me.ckbxAbsenteeBallot And Me.ckbxAbsenteeEnvelope = False Then
                    Exit Sub
                Else

                    If Me.ckbxAbsenteeBallot = True Then
                        oDoc.Envelope.PrintOut , rAddr, , , sAddr, , , , "Size 12"
                    Else:
                        If Me.ckbxAbsenteeEnvelope = True Then
                            oDoc.Envelope.PrintOut , sAddr, , , rAddr, , , , "Size 14"
                    End If
                    End If

                ''/// pause macro for 5 seconds(approx)
                Application.Wait (Now + TimeValue("0:00:10"))

            oWord.Quit False
End Sub

The fix? EVERYTHING had to be inside of the with statement EXCEPT the oWord.Quit False statement otherwise the oWord statement call would quit prematurely. I figured it had to be something simple. Correcting the call order was all that was needed. Thanks again for your help.

The working code:

Private Sub cmbtnPrint_Click()
 Dim otable As Range, r As Long, c As Long, sAddr As String, rAddr As String
 Dim oWord As Word.Application, oDoc As Word.Document
 
        
            If Me.ckbxAbsenteeBallot = False And Me.ckbxAbsenteeEnvelope = False Then
                MsgBox "Please choose which envelope type to print"
            End If
        
        
        With Me.ListBox1
            If .ListIndex = -1 Then Exit Sub '''/// no selection made
                ''///check if the current value is selected
            For iX = 1 To .ListCount
                ''///check if the current value is selected
                If .Selected(iX - 1) = True Then
                    sAddr = .List(iX - 1, 1) & " " & .List(iX - 1, 0) & vbCr & .List(iX - 1, 2) & vbCr & .List(iX - 1, 3) & " " & .List(iX - 1, 4) & " " & .List(iX - 1, 5)
                    rAddr = Sheets("Data").Range("A1").Value & vbCr & Sheets("Data").Range("A2").Value & vbCr & Sheets("Data").Range("A3").Value & vbCr & Sheets("Data").Range("A4").Value & ", " & Sheets("Data").Range("A5").Value & ", " & Sheets("Data").Range("A6").Value
                
                Set oWord = CreateObject("Word.Application")
                Set oDoc = oWord.Documents.Add
                
                If Me.ckbxAbsenteeBallot = True Then
                        oDoc.Envelope.PrintOut , rAddr, , , sAddr, , , , "Size 12"
                    Else:
                    If Me.ckbxAbsenteeEnvelope = True Then
                        oDoc.Envelope.PrintOut , sAddr, , , rAddr, , , , "Size 14"
                    End If
                End If
                
                End If
            Next iX
        End With
            oWord.Quit False            
End Sub

Are you attempting to print more than 1? This doesn't work for me. If i select more than 1 in the listbox, it will only print the last selection in alphabetical order. If its working for you than what could possibly be the issue why it wont work for me? If i select 1, it prints 1, if I select 5, it prints 1, if i select 10, it prints 1... and the one it prints is ALWAYS the last in alphabetical order?? I'm extremely confused now.

I'm a little confused as to How you want this to happen but to insert add a column to a piviot table, you'll need to include this in your code:

Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")

'for empty cells in the data area of a PivotTable report, show a specified value, using the PivotTable.NullString Property:
PvtTbl.NullString = "0"
PvtTbl.DisplayNullString = True

'this would be an example of how to set the field and write the formula
PvtTbl.CalculatedFields.Add Name:="Variance", Formula:="=IF(OR(Sales=0,Budgeted Sales=0),0,(Sales - Budgeted Sales)/Budgeted Sales)"

With PvtTbl.PivotFields("Variance")

.Orientation = xlDataField
.Function = xlSum
.Position = 3 'the position where you want the field
.NumberFormat = "0.00%"
.Caption = "Variance-%" ' the name of the field

End With


End Sub

Hopefully this will give you a start and maybe someone that is a little more familiar with piviot tables can help you refine it to your needs.