19 Replies Latest reply on Mar 18, 2012 4:16 PM by CarlosCanto

    CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA

    duhwellhuh Level 1

      Hey Carlos- Here's the repost. 

       

      Could an XLSM file read a line from a CSV file in "A1" and depending on what was in the first column in the XLSM, search the CSV file and find the indexed item in the second column or third? I've done it before with standard VLookup functions and it was cumbersome and very slow, and for some reason, everytime I would save the file it would take forever although the xls file was a single sheet.

       

      i've attacthed a sketch of what I'm talking about, because it's hard to explain and I think the sketch explains it better. Here it is, sorry if it is blurry, it looked fine before I posted:

      xlsm example.PNG

        • 1. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
          CarlosCanto Adobe Community Professional & MVP

          what's wrong with VLOOKUP? I use it and have no problems with it, in your sample I put the following formula in cell B2

          =VLOOKUP(A3,[yourCSVfile]Sheet1!$A$3:$B$7,2,FALSE)
          

           

          It should have no problem handling large amounts of data, and if you're having problems saving, the culprit could be somewhere else. I'll post the how to do it with vba next.

          1 person found this helpful
          • 2. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
            CarlosCanto Adobe Community Professional & MVP

            here you go, the program will try to find all cells in selection, in the active book (your xlsm file).

             

            - open both files and select a range of cells to look up and...before running, rename "Book2" below with your actual csv file name

             

            Sub xlsmCsvLookup()
                'have both your xlsm and your csv files open
                'in the xlsm file, select a range of cells to look up
                'run the script to search all selected cells and get the values from the csv file
                
                Dim csvDataRange As Range 'csv file data column
                Dim selRange As Range 'to hold selected cells
                Dim foundRange As Range
                
                'get the source range
                Set csvDataRange = Workbooks("Book2").Sheets(1).Range("a:a") ' replace "Book2" with yourCSVfile.csv
                
                'loop thru all selected cells, ignore empty cells
                For Each selRange In Selection.SpecialCells(xlCellTypeConstants)
            
            
                    'try to find it in the csv file
                    Set foundRange = csvDataRange.Find(selRange) 'if found, assign it to foundRange
                    
                    'if no match highligh cut
                    If foundRange Is Nothing Then
                        selRange.Interior.ColorIndex = 34
                    Else
                        'if found, get the value in next cell
                        selRange.Offset(0, 1) = foundRange.Offset(0, 1)
                    End If
                
                Next
                
                Set foundRange = Nothing
                Set selRange = Nothing
                Set csvDataRange = Nothing
            End Sub
            
            • 3. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
              duhwellhuh Level 1

              Yeah, VLOOKUP was quick to get the information after relinking to the external data, but when I tried to save it would take forever. It was like it was trying to save the entire connection. I will try your VBA and let you know how it goes. Thank you so much!

               

              So will this work without the CSV being opened at the same time as the XLSM? If not, is that possible?

              • 4. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                CarlosCanto Adobe Community Professional & MVP

                it is not posible to communicate with a closed file, there are workarounds to get data from it, but once you get the data you'll have to store it somewhere, at least temporarily to be able to do your lookups.

                 

                here's a sample

                http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

                1 person found this helpful
                • 5. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                  duhwellhuh Level 1

                  Alright, the last post from OZGRID won't work as the CSV is dumped every night on a network and I can't place code in there as it would constantly be saved over..

                   

                   

                  I tried the VLOOKUP again and it's not letting me use a CSV. Also, I'm getting a subscript out of range error when I run the script. I did as you instructed and replaced "Book2" with my file which is MyCsv.csv and the error pops up. I also have cells selected. Both files are open as well. Any idea why this would be happening?

                  • 6. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                    CarlosCanto Adobe Community Professional & MVP

                    is it absolutely necessary to access the data without opening the file?

                     

                    I tried the VLOOKUP again and it's not letting me use a CSV.

                     

                     

                    the vlooup formula I posted above, I had it on an unsaved file, after saving the csv, it looks like this

                     

                    =VLOOKUP(A5,[Book2.csv]Book2!$A$3:$B$7,2,FALSE)

                     

                    the sheet name changed after saving, it becames the book name in a csv file.

                     

                     

                    I'm getting a subscript out of range error when I run the script.

                     

                    I don't know why is not working, it should.

                    this is how my sub look like afte saving the Book2.csv file

                     

                    Set csvDataRange = Workbooks("Book2.csv").Sheets(1).Range("a:a") ' replace "Book2" with yourCSVfile.csv
                    

                     

                    is your data in the first sheet?

                    1 person found this helpful
                    • 7. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                      CarlosCanto Adobe Community Professional & MVP

                      by the way, if you add the whole file path in the vlookup formula, it works with the csv closed

                      =VLOOKUP(A1,'C:\Users\carlos\Documents\[Book2.csv]Book2'!$A$3:$B$7,2,FALSE)

                      1 person found this helpful
                      • 8. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                        duhwellhuh Level 1

                        The VLOOKUP was being a pain, but I got the VBA to work. My CSV was on a flash drive and for some reason it was causing the issue. I moved the file to the hard drive and it worked as planned. Thank you so much. I'm going to take this snippet and try to alter it to work across more columns of data. One last question, is it possible to use the VLookup across 2 or more CSV files at the same time using if statements, or is that way too complex?

                         

                        Thanks again!

                        • 9. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                          duhwellhuh Level 1

                          I just found out if I select only 1 cell in the XLSM using the vba code, it still goes through and drops multiple cells back in the XLSM. Is that supposed to happen?

                          • 10. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                            duhwellhuh Level 1

                            by the way, if you add the whole file path in the vlookup formula, it works with the csv closed

                            =VLOOKUP(A1,'C:\Users\carlos\Documents\[Book2.csv]Book2'!$A$3:$B$7,2,F ALSE)

                            On this code(using my file of course), I get a pop which says to update, I need to open the workbook with the connection. Are you not getting that? Sorry to be a pain.

                            • 11. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                              CarlosCanto Adobe Community Professional & MVP

                              =VLOOKUP(A1,'C:\Users\carlos\Documents\[Book2.csv]Book2'!$A$3:$B$7,2,F ALSE)

                              On this code(using my file of course), I get a pop which says to update, I need to open the workbook with the connection. Are you not getting that? Sorry to be a pain.

                              yes, I get it, click ok, it still works, don't need to open the csv. I'm not sure, but I think the other cells need refreshing.

                              • 12. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                                CarlosCanto Adobe Community Professional & MVP

                                One last question, is it possible to use the VLookup across 2 or more CSV files at the same time using if statements, or is that way too complex?

                                mmm...I don't know, describe it better, it might be possible

                                • 13. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                                  CarlosCanto Adobe Community Professional & MVP

                                  I just found out if I select only 1 cell in the XLSM using the vba code, it still goes through and drops multiple cells back in the XLSM. Is that supposed to happen?

                                  no, I didn't try with one cell selected only. What happens is that Excel clycles thru all cells when selection is one cell only, let me see how to fix that. In the mean time select two cells, your lone working cell and another dummy empy cell.

                                  • 14. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                                    duhwellhuh Level 1

                                    One last question, is it possible to use the VLookup across 2 or more CSV files at the same time using if statements, or is that way too complex?

                                    mmm...I don't know, describe it better, it might be possible

                                     

                                     

                                    I figured this one out:

                                     

                                    Here's the code:

                                     

                                    =IFERROR(VLOOKUP(A10,MyCsv.csv!$A$1:$C$6,2,FALSE),IFERROR(VLOOKUP(A10,'C:\Users\Matt\Docum ents\[MyCsv2.csv]MyCsv2'!$A$1:$C$6,2,FALSE),""))

                                    • 15. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                                      duhwellhuh Level 1

                                      =VLOOKUP(A1,'C:\Users\carlos\Documents\[Book2.csv]Book2'!$A$3:$B$7,2,F ALSE)

                                       

                                      On this code(using my file of course), I get a pop which says to update, I need to open the workbook with the connection. Are you not getting that? Sorry to be a pain.

                                      yes, I get it, click ok, it still works, don't need to open the csv. I'm not sure, but I think the other cells need refreshing.

                                       

                                       

                                       

                                      What I've been reading, it actually saves the connection for the cells, like an auto-complete of sorts. Regardless, I'm just using VBA to automatically open those files with "WorkBook_Open" when I open my XLSM file up so that I know I'm getting the right information every time.

                                      • 16. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                                        duhwellhuh Level 1

                                        I just found out if I select only 1 cell in the XLSM using the vba code, it still goes through and drops multiple cells back in the XLSM. Is that supposed to happen?

                                        no, I didn't try with one cell selected only. What happens is that Excel clycles thru all cells when selection is one cell only, let me see how to fix that. In the mean time select two cells, your lone working cell and another dummy empy cell.

                                         

                                        Yeah, that's what I figured. I appreciate your help Carlos!

                                        • 17. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                                          CarlosCanto Adobe Community Professional & MVP

                                          ok, use this version, I changed it a bit. To accommodate the issue with the single cell, I moved the main procedures to a function for a more efficient use.

                                           

                                          Sub xlsmCsvLookup()
                                              'have both your xlsm and your csv files open
                                              'in the xlsm file, select a range of cells to look up
                                              'run the script to search all selected cells and get the values from the csv file
                                          
                                              Dim csvDataRange As Range 'csv file data column
                                              Dim selRange As Range 'to hold selected cells
                                              Dim foundRange As Range
                                          
                                              'get the source range
                                              Set csvDataRange = Workbooks("Book2.csv").Sheets(1).Range("a:a") ' replace "Book2" with yourCSVfile.csv
                                          
                                              'check for selection, only one cell, ceck that cell and exit, otherwise loop thru selection
                                              If Selection.Count > 1 Then
                                                  'loop thru all selected cells, ignore empty cells
                                                  For Each selRange In Selection.SpecialCells(xlCellTypeConstants)
                                                      findSelection selRange, csvDataRange
                                                  Next
                                              Else
                                                  findSelection ActiveCell, csvDataRange
                                              End If
                                          
                                              Set foundRange = Nothing
                                              Set selRange = Nothing
                                              Set csvDataRange = Nothing
                                          End Sub
                                          
                                          
                                          Function findSelection(selRange, csvDataRange)
                                                  'try to find it in the csv file
                                                  Set foundRange = csvDataRange.Find(selRange) 'if found, assign it to foundRange
                                          
                                                  'if no match highligh cut
                                                  If foundRange Is Nothing Then
                                                      selRange.Interior.ColorIndex = 34
                                                  Else
                                                      'if found, get the value in next cell
                                                      selRange.Offset(0, 1) = foundRange.Offset(0, 1)
                                                  End If
                                          End Function
                                          
                                          • 18. Re: CAN AN EXCEL XLSM FILE READ FROM A CSV AND WRITE BACK TO THE SAME XLSM -VBA
                                            duhwellhuh Level 1

                                            It works as it should Carlos. Too bad I used up all of my help and correct answers on this thread earlier to give you more points. You deserve them. Thanks alot!