Free Microsoft Excel 2013 Quick Reference

Renaming text in cell Results

Re: http://www.excelforum.com/excel-prog...ml#post2138532, this is *exactly* the issue that I have. I think Djvice was working with the same UPC db file that I dl from the web, too. I loaded the files in the zip on that thread and got the lookup macro to work fine.

But then I tried with my data file. The dl data file "items.csv" has 1,048,571 rows of data. I renamed djvice.xls to mylookup.xls. I edited the macro to this
Sub aaa()
  Range("B:C").ClearContents
  Set cn = CreateObject("adodb.connection")
  Set rs = CreateObject("adodb.recordset")
  
  cn.Open "provider=microsoft.jet.oledb.4.0;data source = C:Documents and SettingsLenovo_UserMy DocumentsMy Data
Sourcesupcdirectory;extended properties = ""text; hdr=yes"""
  For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  
    rs.Open "select size,description from items.csv where UPC = " & ce.Value, cn, 3, 3
    Cells(ce.Row, 2).CopyFromRecordset rs
    rs.Close
  Next ce
  
  
  
  Set rs = Nothing
  Set cn = Nothing
  
End Sub
where items.csv is my data file. For the header, in the first three cells of the first row of this data file I have inserted "UPC", "size" and "description". When I run the macro I get this error message from MS VB: Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

Help, please. TIA

HI
I am trying to set up vba as part of a macro that will do the following:
Open a master workbook
Open a data workbook( There will be a few of these to process)
Copy the data from a data workbook sheet (INPUT SHEET) to INPUT SHEET in the master workbook.
I will rename the master and then continue with the same process for the next data workbook.
I cannot copy/move the input sheet because of an MS bug whereby all of the sheet coding is lost whe you insert a sheet into a workbook.

I have set up a sheet in the master workbook that lists all of the data workbook names and paths I set the data workbooks up with a string variable name of TRGT.

I can get the coding to open the TRGT workbook but am having trouble getting the subsequent sheet commands to work in order to extract the data.

Here is a snippet of the code:

 
Dim TRGT, TRGTNEXT As String
Dim FILNM As String

Dim MM, NN As Integer


'DEFINE RANGE OF NAMES

For MM = 2 To 20
' NEXT FILE NAME
Sheets("LINKS").Select
'IF NAME ="" THEN END
TRGT = Cells(MM, 1).Value 
TRGTNEXT = Cells(MM, 5).Value 
FILENM = Cells(MM, 3).Value
If FILENM = "" Then GoTo 4444

Workbooks.Open TRGT
From here I want to open the data workbook sheet, select a range of data using sells reference, move the data to the master workbook sheet using the cells reference … then repeat the process with the next range of data. Etc. etc

I need the “open sheet”( activate, select…) text to flip back and forth between the workbooks.

Thanks in advance for your help..

God Bless America !

Hi,

Before I explain my problem, I should let you all know that I posted this same question in this same forum, and this is the link to that forum, but because it hasn't had an answer yet and I found more information for your assistance I started this new thread

http://www.excelforum.com/excel-programming/642554-searching-for-a-string-in-a-cell-and-then-creating-a-sheet-for-that-string.html

Also I posted in this other forum, where I got some responses to my issue and appreciate them, however I tried many times modifying what they suggested and still doesn't work in my situation.

http://www.ozgrid.com/forum/showthre...691#post431691

I will describe my problem now as clear as possible. I'm making a Task list with assignments for each of my team members. Columns A & B describes the ID number of the task, Column C describes the Task itself, Columns D to J are hidden, Column K describes the author of the task.

Column L is the one that have the name (or names) of the person who is in charge of doing that task. What I need to do is to create a macro that searches the name of that person in Column L and once it finds it it will create another sheet with the name of that person. And add to that sheet the entire row of his task.

For example:
|-----------------------------------------------------------------|
|-A-|-B-|--------C---------|---K---|----L------|
|-----------------------------------------------------------------|
|---1---|-----Project A-----|
|--1.01-|-Approve Invoices--|--Jim--|---Dave---|
|--1.02-|--SCC Agreement---|--Jim--|---Victor--|
|-----------------------------------------------------------------|
|-----------------------blank row---------------------------------|
|-----------------------------------------------------------------|
|---2----|-----Project B-----|
|--2.01--|--Planning meeting-|--Jim--|---Victor--|
|--2.02--|-Database update--|--Jim--|---Victor--|
|--2.03--|-Master agreement-|--Jim--|-Victor, Dave-|
|-----------------------------------------------------------------|

This macro should do rename Sheet2 as 'Dave' and it'd contain:
|-----------------------------------------------------------|
|-A-|-B-|--------C--------|--K--|
|-----------------------------------------------------------|
|---1---|----Project A-----|-----|
|--1.01-|-Approve Invoices-| Jim |
|-----------------------------------------------------------|
|------------------------blank row--------------------------|
|-----------------------------------------------------------|
|---2---|----Project B------|-----|
|--2.03-|-Master agreement-| Jim |
|-----------------------------------------------------------|

Then it should rename Sheet3 as 'Victor' and it'd contain:
|-----------------------------------------------------------------|
|-A-|-B-|--------C----------|---K---|
|---1---|-----Project A------|-------|
|--1.02-|--SCC Agreement---|--Jim--|
|-----------------------------------------------------------------|
|-----------------------------------------------------------------|
|---2----|-----Project B-----|---K---|
|--2.01--|--Planning meeting-|--Jim--|
|--2.02--|-Database update--|--Jim--|
|--2.03--|-Master agreement-|--Jim--|
|-----------------------------------------------------------------|

...and it should do the same for any names in the cells of that column, and each cell can contain up to 3 names separated by a comma. Once I run this macro again it should update the information of each sheet created.

I'm new to macros-vba. I don't understand it much, but I found this code online :

Sub PagesByDescription()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String

    Set wSheetStart = ActiveSheet
    wSheetStart.AutoFilterMode = False
    'Set a range variable to the correct item column
    Set rRange = Range("L9", Range("L65536").End(xlUp))
    
        'Delete any sheet called "UniqueList"
        'Turn off run time errors & delete alert
        On Error Resume Next
        Application.DisplayAlerts = False
        Worksheets("UniqueList").Delete
        
        'Add a sheet called "UniqueList"
        Worksheets.Add().Name = "UniqueList"
        
           'Filter the Set range so only a unique list is created
            With Worksheets("UniqueList")
                rRange.AdvancedFilter xlFilterCopy, , _
                 Worksheets("UniqueList").Range("A1"), True
                 
                 'Set a range variable to the unique list, less the heading.
                 Set rRange = .Range("A2", .Range("A65536").End(xlUp))
            End With
            
            On Error Resume Next
            With wSheetStart
                For Each rCell In rRange
                  strText = rCell
                 .Range("A1").AutoFilter 1, strText
                    Worksheets(strText).Delete
                    'Add a sheet named as content of rCell
                    Worksheets.Add().Name = strText
                    'Copy the visible filtered range _
                    (default of Copy Method) and leave hidden rows
                    .UsedRange.Copy Destination:=ActiveSheet.Range("A1")
                    ActiveSheet.Cells.Columns.AutoFit
                Next rCell
            End With
            
        With wSheetStart
            .AutoFilterMode = False
            .Activate
        End With
        
        On Error GoTo 0
        Application.DisplayAlerts = True
        
End Sub

And I tried this one:

Sub SearchForString()
     
    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
     
    On Error GoTo Err_Execute
     
     'Start search in row 4
    LSearchRow = 9
     
     'Start copying data to row 2 in Sheet2 (row counter  variable)
    LCopyToRow = 2
     
    While Len(Range("A" & CStr(LSearchRow)).Value) >= 0
         
         'If value in column E = "Mail Box", copy entire row to Sheet2
        If Range("L" & CStr(LSearchRow)).Value = "Jim" Then
             
             'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
             
             'Paste row into Sheet2 in next row
            Sheets("Sheet2").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste
             
             'Move counter to next row
            LCopyToRow = LCopyToRow + 1
             
             'Go back to Sheet1 to continue searching
            Sheets("Sheet1").Select
             
        End If
         
        LSearchRow = LSearchRow + 1
         
    Wend
     
     'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select
     
     MsgBox "All matching data has been copied."
     
    Exit Sub
     
Err_Execute:
    MsgBox "An error occurred."
     
End Sub

I tried this other one:

Sub GetIt()
    Dim sheetA As Worksheet, sheetB As Worksheet
    Dim wb As Workbook
    Dim i As Long, k As Long
    Set wb = ActiveWorkbook
    Set sheetA = wb.Sheets(1)
     
    i = 9
    On Error Resume Next
    With sheetA
        While Not IsEmpty(.Cells(i, 5).Value)
            Set sheetB = wb.Sheets(.Cells(i, 5))
            If sheetB Is Nothing Then
                wb.Sheets.Add After:=wb.Sheets(wb.Sheets.Count)
                Set sheetB = wb.ActiveSheet
                sheetB.Name = .Cells(i, 5)
                .Rows(2).Copy sheetB.Rows(1)
            End If
            k = sheetB.Range("B65536").End(xlUp).Row + 1
             
            .Rows(i).Copy sheetB.Rows(k)
            i = i + 1
            Set sheetB = Nothing
        Wend
    End With
End Sub

And I just tried this last one:

Sub ExtractToSheets()
    Dim ws     As Worksheet
    Dim wsNew  As Worksheet
    Dim rData  As Range
    Dim rCl    As Range
    Dim sNm    As String
    Set ws = Worksheets("Sheet1")
    Set rData = ws.Range("a1", ws.Range("z65536").End(xlUp))
     
     'extract a list of unique names
    ws.Range("L9", Range("l65536").End(xlUp)).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("i1"), Unique:=True
     
    For Each rCl In ws.Range("i1", ws.Range("i65536").End(xlUp))
        sNm = rCl.Text
         'add new sheet (only if required-NB uses  UDF)
        If WksExists(sNm) Then
            Sheets(sNm).Cells.Clear
        Else
            Set wsNew = Sheets.Add
            wsNew.Move After:=Worksheets(Worksheets.Count) 'move to end
            wsNew.Name = sNm
        End If
         'AutoFilter & copy to relevant sheet
        rData.AutoFilter Field:=2, Criteria1:=sNm
        rData.Copy Destination:=Worksheets(sNm).Range("a1")
    Next rCl
    ws.Columns(9).Delete 'remove temporary list
    rData.AutoFilter 'switch off AutoFilter
End Sub
Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

I'm new to Excel macros-vba, I'm trying to modify any of this codes to work for my problem, but I cannot seem to achieve it. Also, if the code encounter in a the cell more than one name (e.g. Victor,Mike,Jim) instead of copying that row of information to their respective individual sheets, it creates a sheet with those names, and pastes all information there, and that's not what I want.

I'm attaching the file of the Task List itself for your assistance in helping me.

Thank you all in advance. I need your help.

Victor

For the code below, I get the following "Run-Time error '1004'"

"Excel cannot find the text file to refresh this external data range. Check
to make
sure that the text file has not been moved or renamed, then try the refresh
again."

What I can't understand is why the code works fine when I run the program
immediately after receiving the error. It works this way everytime I go to
import another new text file which makes me think it might have something to
do with the default file location being set after the error occurs (?).

Thanks for any insights,
-Tony

Sub GetWFiles(Wpath As String, WhichWfile As Integer)
Dim i As Long, qt As QueryTable, Wdat As String, OrigWSheet As String
'On Error Resume Next

Worksheets.Add After:=Worksheets("OrigTarr")
OrigWSheet = "OrigW"
If WhichWfile <> 1 Then OrigWSheet = OrigWSheet & CStr(WhichWfile)
ActiveSheet.Name = OrigWSheet

'this is where the error occurs first time through only

Wdat = Dir(Wpath & "W*.dat")

Do While Wdat = ""
GetWDirectoryAgain Wpath, WhichWfile
Wdat = Dir(Wpath & "W*.dat")
Loop
i = 0
Do While Wdat <> ""
i = i + 1
Cells(1, i).Value = Wdat
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Wpath & Wdat, Destination:=Cells(2, i))
.Name = Left(Wdat, Len(Wdat) - 4)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
'.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Wdat = Dir()
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next
Loop
End Sub

I’m stumped… It may just be because this newbie has not mastered variables…

In this worksheet module code I am expecting the “ImportRequested” variable
to be set to “True” when the routine triggered by selecting the B1 cell is
completed successfully.

If I test the “ImportRequested” variable before if leaves the B1 routine End
If, it indeed holds the value of True. However when I immediately select the
N1 cell (only for debugging, “ImportRequested” variable = False. I expect it
to still be True and don’t see how it’s getting changed back to False’

Key references:

'<------- 1 I created the “ImportRequested” variable As Boolean
'<------- 2 Code for reference only for debug so when N1 is selected it
displays the value of the “ImportRequested” variable. It always says false, I
would expect it to say True after running the B1 cell routine… ;-(

'<----- 3 The B1 routine has run so set the “ImportRequested” to True

'<------ 4 Test BEFORE leaving the routine “ImportRequested” is TRUE as
expected.

(once I can figure out haw to have this “ImportRequested” variable hold it’s
value, I intend to test for it to bypass the “"Are you sure you want to CLEAR
this Worksheet?” prompt if the import option was just taken…)

Can you please shed some light as to what’s happinging?

-------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Dim racePark As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String
Dim ImportRequested As Boolean '<------- 1

Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

racePark = Left(srcProgramDataInputWs.Range("H3").Value, 3)

If Target.Address = "$A$1" Then
Dim exists As Boolean
Dim ExistingBettingWsName As Worksheet
Dim NewBettingWsName As Variant

Range("N3").Select

NewBettingWsName = Format(srcProgramDataInputWs. _
Range("F3").Value, "mm-dd ") & _
Left(srcProgramDataInputWs.Range("H3").Value, 3)

exists = False
For Each ExistingBettingWsName In ThisWorkbook.Sheets
If ExistingBettingWsName.Name = NewBettingWsName Then
exists = True
Exit For
End If
Next
If exists Then
MsgBox "Betting Worksheet for [ " & NewBettingWsName & _
" ] already exists. [RENAME] or [DELETE] that Worksheet and try
again."

Else
Dim NewBettingWs As Worksheet
Dim NewBettingWsTabColor As Variant
Dim raceParkList As Variant
Dim src As Variant

i = 6
raceParkList = srcProgramDataInputWs.Range("N" & i).Value
Do Until raceParkList = ""
raceParkList = srcProgramDataInputWs.Range("N" & i).Value
If racePark = raceParkList Then NewBettingWsTabColor =
srcProgramDataInputWs.Range("O" & i).Value
i = i + 1
Loop
Range("N3").Select

srcBettingTemplateWs.Copy before:=ActiveSheet
Set NewBettingWs = ActiveSheet
With NewBettingWs
.Name = NewBettingWsName
.Unprotect
.Tab.ColorIndex = NewBettingWsTabColor 'or replace with
index number

src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcBettingTemplateWs.Rows("11:22").Copy .Cells((j * 12)
+ 11, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop

.Protect
End With
End If
End If

If Target.Address = "$N$1" Then '<------ 2 Reference only for debug
MsgBox "End of B1 loop " & ImportRequested
End If

If Target.Address = "$K$1" Then
Dim ResetWorksheet As Boolean
If ImportRequested = True Then
ResetWorksheet = True
Else
If MsgBox("Are you sure you want to CLEAR this Worksheet?", _
vbYesNo) = vbYes Then
ResetWorksheet = True
End If
End If

If ResetWorksheet = True Then
src = ""
ActiveSheet.Unprotect
ActiveSheet.Range("A3:AZ300").Clear
With ActiveSheet
src = srcProgramDataInputWs.Range("B3").Value
i = 3
j = 0
Do Until src = ""
srcProgramSummaryTemplateWs.Rows("3:14").Copy .Cells((j
* 12) + 3, 1)
i = i + 12
j = j + 1
src = srcProgramDataInputWs.Cells(i, 2).Value
Loop
ActiveSheet.Protect
Range("K1").Value = "default"
Range("N3").Select
End With
End If
'ImportRequested = False
ResetWorksheet = False
End If

If Target.Address = "$B$1" Then
Dim SelectedTxtInputFile As Variant
SaveDriveDir = CurDir
MyPath = ThisWorkbook.Path & "/RaceData-XLS-Ready"
ChDrive MyPath
ChDir MyPath

SelectedTxtInputFile = Application.GetOpenFilename( _
"Race Program Input Files (*.txt),*.txt", , _
"Select which RACE Program to import", , False)

If SelectedTxtInputFile = "False" Then
Range("N3").Select
Else
ImportRequested = True '<----- 3

srcProgramDataInputWs.Unprotect
srcProgramDataInputWs.Range("A3:H242").ClearContents

With srcProgramDataInputWs.QueryTables.Add(Connection:= _
"TEXT;" & SelectedTxtInputFile _
, Destination:=srcProgramDataInputWs.Range("A3:H242"))
.Name = "ImportProgramData"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
srcProgramDataInputWs.Protect
End If
'Range("K1").Select
ChDrive SaveDriveDir
ChDir SaveDriveDir
MsgBox "End of B1 loop " & ImportRequested '<------ 4
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
'Range("K1").Value = "Clear"
ws_exit:
Application.EnableEvents = True
End Sub

Hello,

Usually UPC Codes have a certain number of spaces, they are numeric, and may even have some form (a template they follow).
My case is none of the above. Every conceivable combination has been used. See the bottom of this message for an example of just some of the formats I must deal with! As you can see, I can not adhere to the simplest of remedies.
Thank You for your time In Advance!

I'm trying to help a friend however, I'm not doing so well. They have UPC codes in their POS (Point of Sale) that include EVERYTHING! Text, ISBN Numbers (like a specialized UPC code for books), 1 character, 1 number all the way up to about 20 characters long that include alpha numeric, and dashes. There is NO consistency with it. Thus, I cant make a Template for it that says it must use 20 spaces, or I will not get a match for the POS to call up a price for the item scanned!
Does anyone have an idea?!

My main problem seems to be that I have a number of UPC numbers that start with Zeros. The UPC can be anywhere from 7 digits long to about 13, and the leading zeros can be anywhere from NONE to Five! If I loose the leading Zero(s) - I don't get a match when the POS scans in the UPC code. and looks for the price for the associated item.

I Exported their entire inventory into the only thing that the system would export/output it to, which was a CSV file. After about 8 days of messing things up, I found out the best way to open it - to PRESERVE the leading Zero(s) was to rename the file to a .TXT file Then open it with EXCEL which would then walk me through the steps to ensure I was saving those fields as TEXT fields to keep them precious little placeholders (zero(s)) for me!

My task at hand is this:
I have all the information I need on the Exported file, EXCEPT for two columns which are called SUPPLIER and REORDER No.

Wouldn't ya know... on another file I saved as a .TXT file, I opened in the same manner as described earlier, I have that information along with with the UPC Numbers (in text fields) When trying to do a VLOOKUP My result in the box is "NA"

In any case, Ive determined that I can NOT use the UPC numbers as they are TEXT to to fill in the desired SUPPLIER and REORDER numbers that are on the 2nd sheet!

Can Anyone Pleeeese give me a push in the right direction here.
1. WHAT is the Criteria needed to preform an EFFECTIVE VLOOKUP?
2. WHAT are the Dos/DONTS ... Surely there must be SOME webpage somewhere that describes this!

Here is what Im doing and its break down...
=VLOOKUP(the UPC # from which I am referencing, Highlighting the cells where Excel might get the info from {ie A2:L10435}, the column # {ie b=2, J=10}, and a Zero at the end cuz I want an exact match) ends up looking something like this:
=VLOOKUP(C$2,'REORDER No'!A$2:N$10429,11,0)

I Thank You again for your help!
Gratefully

Below are a handful of examples of the UPC codes that are in this POS system. As you can see, they have adopted various methods of identification of their products, UPC, ISBN, VENDORS SKUs, Names, etc .. thus making this difficult. Any suggestions you might have would be extremely helpful!

Again - I thank You!

000
0 FW2
0003
3 HTG-1052
00036146
36146 HTG-116
00036748
36748 LIFE 5000
0004
4 U707529029078
0005
5 46453-7
0653730059918109
6.5373E+14 992C
08068-80410-2
A
0-37387334-4
08068-81117-2
A101
0-8307-1839-7
08068-81628-25
BACON
0-87983-939-2
08068-83953-22
BB-6
0-916035-30-1
099999200249
99999200249 0-310-92322-0
099999251418
99999251418 0-373-87336-00
1-56108-004-7
1/4 LB PATTIES
103B
1-56108-005-5
1038C

183448000556
1.83448E+11

NOTICE how the number in the last row was converted from the left column to the 1.83...+11 when converted to GENERAL (or Numeric)

VLOOKUP, UPC, leading zeros, POS

I have a macro in a worksheet that works in conjunction with a VLOOKUP to
display pictures based on the VLOOKUP. The appropriate picture displays at
the P16 cell location. My problem is when I put the macro in, it makes ALL
my graphic objects (PICTURE) disappear except for the one the VLOOKUP calls
for. How can I exclude some of the other graphic objects that are on the
sheet? I tried renaming the objects and it still didn't work i.e.(Picture
100 to Pic 100 and also tried Obj 100). It know someway that it's a graphic.
Here's the code I used:

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("P16")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub

Hello everyone!

I have a problem, and while I do have backup files (a week old), I cannot think of any reasonable explanation for what is happening.

Last night a co-worker was working on a Shared File, was doing some text inputs (adding dates & notes) and some format changes (highlighting certain cells). They saved and closed out the file (in .xlsx format as it has always been) using Excel version (12.0.6504.5001) SP1 MSO (12.0.6320.5000).

This morning, when the tracker was being opened, an error displayed as follows:
"Excel found unreadable content in 'filename.xlsx'. Do you want to recover teh contents of this workbook? If you trust the source of this workbook, click Yes. (Yes) (No)" I hit Ctrl-Shift-i to get the code for the error (101648)

Clicking no the first time and making a copy of the file, then click Yes. Yet another error is displayed as follows:
"Excel cannot open the file 'filename.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. (OK)" I hit Ctrl-Shift-i to get the code for that error (101590)

I then tried to open a new workbook and link it to certain cells, but it morphs my formula and returns a #REF error

The original formula I type in is:

The formula that I see after I hit enter is:

Which is just odd in and of itself, so then I started browsing google to see if anyone else had encountered such an issue,
and while I could find responses on the errors alone, I couldn't find anything that matched what was happening to me. 

I also tried to rename the file to .xls and .xlsb and tried to open the file. As well as using Excel 2003 with the converter to open the file.

So far everything I have tried has failed to work.

Has anyone seen anything like this? Have any thoughts on solutions?

Your help is greatly appreciated!

Thank You,
John

Disclaimer: I am a beginner with VBA.

I started off trying to create a VBA code that would extract certain text from a folder full of .msg files (Outlook 2007) and put them in a a blank workbook.

After a lot of google it seemed to me that its impossible to get a text stream to parse from msg files. So I tried to save a few of the msg files as txt files and used the following code on it. It worked fine!

Now the challenge is, I tried a code to batch rename the extension of the .msg files to .txt files. Just renaming the files (Not SaveAs txt) results in files with a lot of garbage headers in the file and the code doesn't work.

Please help me with some code which would either:
A> create a text stream directly from .msg files OR
B> copy the legible section of the renamed files into a temporary txt file for parsing OR
C> modify the code to work in spite of the garbage headers OR
D> any other ideas to make this work that I didn't think of.

Please check the attached zip files with all the working files.

Sub CRMIDCollate()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim wkbk As Workbook
Dim wks As Worksheet

'change to point at the folder to check
MyPath = Worksheets(1).TextBox1.Text

If Right(MyPath, 1) <> "" Then
    MyPath = MyPath & ""
End If

'just in case the path isn't correct.
On Error Resume Next
    myFile = Dir(MyPath & "*.txt")
On Error GoTo 0

If myFile = "" Then
    MsgBox "no files found"
    Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile <> ""
    fCtr = fCtr + 1
    ReDim Preserve myFiles(1 To fCtr)
    myFiles(fCtr) = myFile
    myFile = Dir()
Loop

If fCtr > 0 Then
    'some housekeeping
    myStrings = Array(LCase("Following are the Single Sign On login details for "), LCase("SSO User ID:
"), LCase("Password: "))
    
    TotalExpectedValues = UBound(myStrings) - LBound(myStrings) + 1
    
    Set wks = Workbooks.Add(1).Worksheets(1)
    wks.Range("a1").Resize(1, TotalExpectedValues).Value = Array("Name", "CRM ID",
"Password")
    
    For fCtr = LBound(myFiles) To UBound(myFiles)
        Call DoTheWork(MyPath & myFiles(fCtr), wks)
    Next fCtr
    
    wks.UsedRange.Columns.AutoFit
End If

End Sub


Sub DoTheWork(myFileName As String, wks As Worksheet)

Dim myNumber As Long
Dim myLine As String
Dim FileNum As Long
Dim oRow As Long
Dim FoundValues As Long
Dim iCtr As Long

With wks
    oRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

wks.Cells(oRow, "A").Resize(1, TotalExpectedValues).Value = "Error"

FileNum = FreeFile
Close FileNum
Open myFileName For Input As FileNum

FoundValues = 0

Do While Not EOF(FileNum)
Line Input #FileNum, myLine
myLine = Trim(myLine) 'get rid of all leading/trailing spaces
For iCtr = LBound(myStrings) To UBound(myStrings)
If LCase(Left(myLine, Len(myStrings(iCtr)))) = myStrings(iCtr) Then
FoundValues = FoundValues + 1
wks.Cells(oRow, "A").Offset(0, iCtr).Value = Mid(myLine, Len(myStrings(iCtr)) + 1)
End If
If FoundValues = TotalExpectedValues Then
Exit For
End If
Next iCtr
Loop

Close FileNum

End Sub


I used the same formula as the previous quarter that worked. I copy and pasted new data(from same source that previous quarter data came from) and I am receiving all #N/A errors. I used the same file and renamed the file to 12Q1 rather than the 11Q4 last quarter. The formula is below. I have checked all the referencing cells in the formula and they are all correct. I just do not know why I am receiving #N/A. Did the data possibly come in as text rather than number, therefore it isn't recognizing the "match"? Any help would greatly be appreciated. Thanks!

=INDEX('TBSTATMG UC drilled'!$B$6:$U$938,MATCH($A7,'TBSTATMG UC drilled'!$B$6:$B$938,0),MATCH($D$3,'TBSTATMG UC drilled'!$B$6:$U$6,0))

Basically i'm building a database in access with all the data handling done across a network in excel.(both 2007) Current problem is validation.

The plan is to have code that takes a validated cell i.e. one that has a list associated with it from the excel built in validation tool and populate a temp combo box with that list, this means that every time a cell is selected a validated list can be chosen from, as well as this as the text is typed in the list is autocompleted!..

i had this working for one sheet, but when i try to create a new instance of it in a new worksheet it fails..

can anyone tell me why?

thanks

Jonathan

Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
'Dim wsList As Worksheet
Set ws = ActiveSheet
On Error GoTo errhandler
'Set wsList = Sheets("ValidationLists")


If Target.count > 1 Then GoTo exitHandler

Set cboTemp = ws.OLEObjects("TempCombo")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If

  On Error GoTo errhandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 15
      .Height = Target.Height + 5
      .ListFillRange = str
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If

exitHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub
errhandler:
  Resume exitHandler

End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
Private Sub TempCombo_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'Tab
            ActiveCell.Offset(0, 1).Activate
        Case 13 'Enter
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select
End Sub
update..

turns out all that needs to be done was renaming a combo box "TempCombo".. not a clue how i missed this.. wasted the better part of a day.. anyway, feel free to use the code if it's helpful fo you, originally from http://www.contextures.com/

I have managed to create a table of text with randomly assigned locations. In addition, I have renamed each and every cell within the table (Not the same name! Example = _101, _102, etc.)

On another worksheet, I'd like to search through the table for a specific text and then return the defined name of the matching cells into a list.

Is this possible?

Definately not a guru


No luck finding an answer? You could always try Google.