Greetings,
I've been lurking here for years as it's a great source of information for previous problems I've had, however I'm running into one that seems to be killing me.
I've been tasked with designing an Import Mechanism for some excel files we receive. Basically what I have to do because we have no control over the format of this file is to open the Workbook using OLE and determine what the range is using certain static delimiters. Then pass that range to a DoCmd.Acimport function
It works for using a particular string for me to find the top portion of the range, "Subhead", however when finding the bottom, If I merely give it the string "- 1 -" (two spaces on each side), which, hex editor confirmed on a copy-paste out, is the exact string. However if I save it as a CSV I get "-ÿ 1 ÿ-" for that field.
The Code I'm using to find it is here, only the Position1 Line works...
Edit: Passing "- 1 -" does not work either, vice using the run on Chr Functions.
If I pass as the parameter of the function, the value of the range it should find, it works:
So my question is, is there something I'm missing in terms of what to pass it? Is there some sort of Cryptic code that I can set a Cell equal to, perhaps Bit or Hex wise so I know I'm capturing everything in the search string? Or perhaps a way to hard code the contents of a range to find?
I'm also open to ideas or other ways to skin this cat, and appreciate any feedback whatsoever! Thanks!
I've been lurking here for years as it's a great source of information for previous problems I've had, however I'm running into one that seems to be killing me.
I've been tasked with designing an Import Mechanism for some excel files we receive. Basically what I have to do because we have no control over the format of this file is to open the Workbook using OLE and determine what the range is using certain static delimiters. Then pass that range to a DoCmd.Acimport function
It works for using a particular string for me to find the top portion of the range, "Subhead", however when finding the bottom, If I merely give it the string "- 1 -" (two spaces on each side), which, hex editor confirmed on a copy-paste out, is the exact string. However if I save it as a CSV I get "-ÿ 1 ÿ-" for that field.
The Code I'm using to find it is here, only the Position1 Line works...
Code:
Set Position1 = WBExcel.Sheets("Page2").Range("A1:V65000").Find("Subhead", XLApp.ActiveCell, xlValues, xlWhole, xlByRows, xlNext)
Set Position2 = WBExcel.Sheets("Page2").Range("A1:V65000").Find(Chr(45) & Chr(255) & Chr(32) & Chr(49) & Chr(32) & Chr(255) & Chr(45), XLApp.ActiveCell, xlValues, xlWhole, xlByRows, xlNext)If I pass as the parameter of the function, the value of the range it should find, it works:
Code:
Set Position2 = WBExcel.Sheets("Page2").Range("A1:V65000").Find(WBExcel.Sheets("Page2").Range("$B$15495").Value, Position1, xlValues, xlWhole, xlByRows, xlNext)I'm also open to ideas or other ways to skin this cat, and appreciate any feedback whatsoever! Thanks!
