Consulting

Results 1 to 5 of 5

Thread: Excel add-in problem, workbook.

  1. #1
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    266
    Location

    Excel add-in problem, workbook.

    I attached the work so far.
    It needs to be "installed" in the C:\Users\ <<USER>> \AppData\Roaming\Microsoft\AddIns\
    Folder and activated in the excel options "Add-ins" menu.

    Once all that is done close excel and open the CSV-file in the zip.
    You will get a question if you wish to run a macro, yes.

    Now, here comes the problem.
    As you could see, changes where made to the sheet/workbook.

    In the xlam file there is a module code also, this code is supposed to "undo" the formating.
    And it works (if you uncomment the 3 upper rows), but my plan is to make this code easier to run.
    Because as of now you have to open the VBA window, open the module and run it.

    By creating a new sheet in the workbook, and then rename it to "CSV" without "", the next time you open that sheet it should activate the modulecode.
    But the code crashes at wb.Sheets(1).Cells.Select

    And thats the big question, why?
    The code works as a modulecode but not when it's called from 'ThisWorkbook'.

    Or is there a simpler way to activate this code by the user, when the user wants it.
    It can not be automated as the rest.

    Appreciate any help.


    When you "uninstall" this add-in you must delete the folder C:\Flysight\ also which this add-in creates.
    Attached Files Attached Files

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I might be misunderstanding your problem, but I get no question when I open the CSV file.

    I also see problems with your code. This

    [vba] wkbName = Workbooks(1).Name
    [/vba]

    could get you the wrong workbook, why not use

    [vba] wkbName = wb.Name[/vba]

    and this

    [vba]
    If extension = ".CSV" And Range("A1").Value = "time,lat,lon,hMSL,velN,velE,velD,hAcc,vAcc,sAcc,gpsFix,numSV" Then
    [/vba]]

    will never pass as true because that string will break into separate cells when the workbook is opened.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    266
    Location
    Quote Originally Posted by xld
    I might be misunderstanding your problem, but I get no question when I open the CSV file.

    I also see problems with your code. This

    [vba] wkbName = Workbooks(1).Name
    [/vba]

    could get you the wrong workbook, why not use

    [vba] wkbName = wb.Name[/vba]

    and this

    [vba]
    If extension = ".CSV" And Range("A1").Value = "time,lat,lon,hMSL,velN,velE,velD,hAcc,vAcc,sAcc,gpsFix,numSV" Then
    [/vba]]

    will never pass as true because that string will break into separate cells when the workbook is opened.

    I see what you are saying about the workbook name.
    But it has not created any problems so far with this code.

    So your excel opens the CSV-file as one data per column and not everything in column A?
    Huh...
    Thats odd.
    Are you sure thats not a setting you got?

    My Excel is stock 2007 version, and it opens with everything in column A.
    I understand that you don't get the question if it breaks up the file like that.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Mine is stock 2010, and I am not aware of any setting that I have made that should make it do so. Even if all in column A, I cannot see why you get the macro question, a CSV has no macros.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Tutor
    Joined
    Jan 2008
    Posts
    266
    Location
    No CSVs do not have macros, but thats why it's a Excel add-in.
    Every time I open a CSV file, I want excel to determin if it is a Flysight GPS CSV file or 'any CSV'.
    If it is a Flysight CSV it will prompt the question and the macro can start.

    This code below makes sure it is a flysight CSV that has been opend.
    [vba]If extension = ".CSV" And Range("A1").Value = "time,lat,lon,hMSL,velN,velE,velD,hAcc,vAcc,sAcc,gpsFix,numSV" Then
    Response = MsgBox(prompt:="Run Flysight GPS-Script?", Buttons:=vbYesNo)
    If Response = vbNo Then
    Exit Sub
    End If
    Else
    Exit Sub
    End If[/vba]

    I attached a version I think will work in 2010 version.
    It's slightly different at finding out if it's a Flysightfile and does not 'text-to-columns' to the text, but apart from that it's the same.

    I also attached a copy of the CSV file when the macro has been run.
    Sheet5 is manually created, but it shows what I want.
    A sheet that looks like it used to do.

    When the macro has been run, the user needs to clean up the data.
    In this file it means delete all rows except ~741~1041.
    Thats the data the user needs, the data before row 741 is to make sure the GPS has a good fix on the sattelites.
    And the data after is because you can't see the device and it's hard to turn off in the air.

    Once the user has deleted the unwanted data the charts function as they should and I want to create a new file that looks like the original file but only has the "intresting" data in it.

    As you can see in sheet5 I tried to use excelfunctions, but when you delete the rows from the first sheet you get #ref error.
    Basicly what I need is either help with the macro or with the functions in such way that they will always link to A3, B3, C3 etc. even if I delete the rows from the source sheet.

    Hope this helped better to clear the problem.
    Thanks.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •