none
Parsing a textfile from Access into Excel using VBA RRS feed

  • Question

  • Hi Everyone,

     

    I inherited this code that is suppose to parse a textfile from Access into an excel spreadsheet using VBA.  There is a form where the user selects the textfile in Access.  The code works great the first time. The second time through it always bombs out on the line ActiveSheet.Range("A1").Select  highlighted below.  Error message is words to the effect that the Object variable or with Block variable is not set.  Which I don't understand because I thought that was done on the line above the error.

     

    The way this is suppose to work

    1).  User selects text file from access application

    2) vba opens text file in excel

    3). macro parse first column in excel spreadsheet to find the first character in cell that is an A as in  A0156.  This is done with a find command and it works great the first time through.

    4). close the text file

    5).  Ask the user if they would like to save the excel file

    6). Close excel

     

    I'm relatively new to VBA. My understanding of what I've read about Access VBA is that it's a bit more difficult.  I've tried Dim objExcelApp As object and other stuff.  Nothing seems to work.  I'm  really stuck and need some help.

     

    Here is the code snippet. 

     

    ...

     

    Dim objExcelApp As Excel.Application

    Dim objWb As Excel.Workbook

    Dim Sht As Excel.worksheet

     

    Set objExcelApp = New Excel.Application

    objExcelApp.Workbooks.OpenText Filename:=DataSourcePath, DataType:=xlDelimited, ConsecutiveDelimiter=True, Space:=True

    Set objWb = objExcelApp.ActiveWorkBook

    ActiveSheet.Range("A1").Select   (ERROR OCCURS HERE)

    Selection.end(xldown).select

    Count = Range ("A65536").End(xlUp).Row -1

    ActiveSheet.Range("A1"). Select

     

    This is where the find goes. 

     

    Please let me know what I am doing that is wrong with examples.

    Thanks,

    Drew

    Saturday, June 16, 2012 10:49 PM

All replies

  • When you use Automation to control Excel from Access, you must take great care to make ALL Excel objects that you use refer directly or indirectly to the Excel.Application object that you create - objExcelApp in your code.

    The lines

    ActiveSheet.Range("A1").Select
    Selection.end(xldown).select
    Count = Range("A65536").End(xlUp).Row -1 
    ActiveSheet.Range("A1").Select

    all use Excel objects without referring to objExcelApp, so they become "orphans". The correct way to do it is

    objExcelApp.ActiveSheet.Range("A1").Select
    objExcelApp.Selection.End(xlDown).select
    Count = objExcelApp.Range("A65536").End(xlUp).Row - 1 
    objExcelApp.ActiveSheet.Range("A1").Select

    Apart from that, several of the lines are redundant. The only one you need here is the third one, I think.


    Regards, Hans Vogelaar

    Sunday, June 17, 2012 12:07 AM
  • Thanks for the tip.  I got rid of the error at the ActiveSheet Select.

    Now I have an error Type mismatch at the find statement. 

    I added the objExcelApp to the front of ActiveSheet in the remainder of the Sub.

    Here's where the error is:

    objExcelApp.ActiveSheet.Cells.Find (What:= "---" After:ActiveCell, Lookin:=xlFormulas, LookAt: = _  (TYPE MISMATCH ERROR HERE)

    , SearchFormat:=False).Active

    nSrRow = ActiveCell.Row+1

    Any suggestions?

    I was thinking about trying this:

    objExcelApp.ActiveSheet.Cells.Find (What:= "---" After:objExcelApp.ActiveSheet.ActiveCell, Lookin:=xlFormulas, LookAt: = _  (TYPE MISMATCH ERROR HERE)

    , SearchFormat:=False).Active

    nSrRow = objExcelApp.ActiveSheet.ActiveCell.Row+1

    Thanks,

    Drew

    Sunday, June 17, 2012 3:28 AM
  • Without all of your code for the procedure it is hard to determine exactly what the problem is. I can see some things which might cause a problem but they don't seem to return the Type Mismatch error.

    Ensure that Excel is the Active Application with the following line of code before the Find.

    objExcelApp.Activate

    Your code is missing some of the parameters. This may be due to your editing in the post to insert the bold type where error occurs. ( LookAt: = should have xlWhole or xlPart after it.)

    The end of the line should be Activate; not Active.

    The particular code will return an error if the string being searched for is not found. There are better ways to code this but really need to see all the code for the procedure to determine how it should be coded.

    If you are still having problems then I suggest that you post all of the procedure code for testing and editing. Will also need to know whether you are using Early or Late binding. To check if using Early binding:

    In the VBA Editor select menu item Tools -> References

    Look to see if you have a box checked against "Microsoft Excel nn.n Object library" where nn.n is the version. If it is checked then you are using early binding but if not checked then it is late binding.

    What version of Office are you using?


    Regards, OssieMac

    Sunday, June 17, 2012 4:41 AM
  • You're missing a comma between "---" and After:, and a = between After: and objExcelApp.

    You don't specify a value for LookAt - this should be either xlPart or xlWhole.

    There shouldn't be a space between LookAt: and =

    Active is not a valid operation, I suspect you want Select

    objExcelApp.ActiveSheet.Cells.Find(What:= "---", _
    After:=objExcelApp.ActiveSheet.ActiveCell, Lookin:=xlFormulas, _
    LookAt:=xlWhole, SearchFormat:=False).Select


    Regards, Hans Vogelaar

    Sunday, June 17, 2012 9:17 AM