Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    Tvanduzee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    39
    I began to start on the script in excel to get all the data together, but run into errors in the script:



    Code:
    Sub getalldata(fname)
    Dim pth As String
    Dim ws As Worksheet
    Dim wb_to_find As Variant
    Dim cellA, CellB, CellC, CellD, CellE, CellF, CellG, CellH, CellI, CellJ As Range  ' wasn't sure how many ranges I would need.
    pth = "C:\Users\Terry\Desktop\Kobes Files\workorders\Files\"
    wb_to_find = pth & fname
    With Workbooks(wb_to_find).Worksheets(Left(fname, 4)) 'message saying subscript out of range
        Set ws = Sheets(Left(fname, 4))
        cellA = Range("B2:B6")
        CellB = Range("D2,D4,D6")
        CellC = Range("E1")
        MsgBox ws.UsedRange.Rows.Count
        CellD = Range("A8:J" & ws.UsedRange.Rows.Count)  'this range is the range that contains the details for the workorder.
        
        filetoopen = pth & fname
        Call trial(pth, fname)
    End With
    
    End Sub
    Sub LoopThroughFiles()
        Dim MyObj As Object, MySource As Object, file As Variant
       file = Dir("C:\Users\Terry\Desktop\Kobes Files\workorders\Files\")
       While (file <> "")
         Call getalldata(file)
         file = Dir
         
      Wend
    End Sub
    When I put the data into the tables, the wo_details table will probably contain all the data from the range a8:j last row. I have not tested it because of the one error while trying to step through it.

  2. #17
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    With Workbooks(wb_to_find).Worksheets(Left(fname, 4)) 'message saying subscript out of range
    maybe the "worksheets(name)" doesn't work. maybe it has to be "worksheets(sheet index)"by the way, are you opening the workbook before doing any of this? you can't manipulate xl data until you open the file first. (e.g. => set wkbk object = workbooks.open(fname))is this all the code you have so far? most of it looks great!!!

    2 suggestions though, aside from opening the workbook first:

    1) the Range() object takes only 2 args. you have to use syntax that provides an a range that is not broken. like (A1:A5). you can't do things like (A1, A3, A5).

    2) I would not use multiple string variables for small amounts of data. if you just need to order the data, or say re-arrange it in excel as a preparation for import, just use the syntax RANGE("CELL") when referencing. no need for strings just to re-arrange data.Are those some good tips? You're pretty much right on as it is now. also, you can loop an unbroken range of cells in one column (cell ranges that don't have blank cells blocking start to end) via this method:

    Code:
    range("A1", RANGE("A1").END(XLDOWN))
    you can also look up, left and right using that property.Try those little tweaks (if you want to continue to do it yourself) and let me know what changes. Here's what my version of your code would look like (pseudo code):

    RED => my code.
    GREEN => not sure what you're doing.

    Code:
    Sub getalldata(fname)
    Dim pth As String
    Dim ws As Worksheet
    Dim wb_to_find As string
    dim wkbk_open as workbook
    pth = "C:\Users\Terry\Desktop\Kobes Files\workorders\Files\"
    wb_to_find = pth & fname
    
    set wkbk_open = workbooks.open(wb_to_find)
    
    With wkbk_open .Worksheets(Left(fname, 4))
    
        'discard variables and just work with ranges directly here...
        'you can simply use .RANGE(arguments) in this section because you're already inside the worksheet you need 
        (WITH block)
    
        filetoopen = pth & fname 'WHAT IS THIS LINE?  IF TRYING TO OPEN WKBK HERE, NO NEED NOW.
        Call trial(pth, fname) '????? ROUTINE PURPOSE?
    
    End With
    
    End Sub

  3. #18
    Tvanduzee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    39
    I have revised my code, It will work, but I have to run it several times.

    Code:
    Sub LoopThroughFiles()
        Dim MyObj As Object, MySource As Object, file As Variant
       file = Dir("C:\Users\Terry\Desktop\Kobes Files\workorders\Files\")
       While (file <> "")
         Call getalldata2(file)
         file = Dir
         
      Wend
    End Sub
    Code:
            Sub getalldata2(fname)
            Dim pth As String
            Dim ws As Worksheet
            Dim wb_to_find As String
            Dim wkbk_open As Workbook
            pth = "C:\Users\Terry\Desktop\Kobes Files\workorders\Files\"
            wb_to_find = pth & fname
    
            Set wkbk_open = Workbooks.Open(wb_to_find)
    'This needs to be run several times go get several ranges
    
            With wkbk_open.Worksheets(Left(fname, 4))
                .Activate
                .Range("d2,d4,d6").Copy
            End With
                    With ThisWorkbook.Worksheets("Sheet1").Activate
                        '.Activate
                        Worksheets("Sheet1").Cells (Range("A65536").End(xlUp).Row + 1)
                        .Select
                    
                        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
                    End With
        
        'This takes the WorkOrder details and puts it on a second sheet,  needs to be run 2 times so the workorder number can be put above it  first (changing ranges each time)
        
            With wkbk_open.Worksheets(Left(fname, 4))
                .Activate
                .Range("A8:J" & .Range("A65536").End(xlUp).Row).Copy
            End With
                    With ThisWorkbook.Worksheets("Sheet2").Activate
                        Worksheets("Sheet2").Range("A2").Select
                    
                        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                    End With
    
           End Sub
    [/code]

    Im trying to find a way so that I don't need to hard code each time I need a new range.

    Thank you for your code suggestions, it helped quite a bit.

    Terry

  4. #19
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    well first of all, I localized your code and ran it on an open book that was a replica of your own. the code got smaller, and mine was:

    Code:
    Function getalldata2()
            Dim pth As String
            Dim ws As Worksheet
            Dim wkbk_open As Workbook
            Set wkbk_open = ActiveWorkbook
    
    
    'This needs to be run several times go get several ranges
    
    
    Application.ScreenUpdating = False
    
    
            wkbk_open.Worksheets("Sheet1").Range("d2,d4,d6").Copy
                wkbk_open.Worksheets.Add
                wkbk_open.Worksheets(1).Activate
                wkbk_open.Worksheets(1).Cells(Range("A65536").End(xlUp).Row + 1).Select
                Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    
        'This takes the WorkOrder details and puts it on a second sheet,
        'needs to be run 2 times so the workorder number can be put above it
        'first (changing ranges each time)
        
            wkbk_open.Worksheets("Sheet1").Range ("A8:J") & _
            wkbk_open.Worksheets("Sheet1").Range("A65536").End(xlUp).Row.Copy
    
    
            wkbk_open.Worksheets(1).Activate
            wkbk_open.Worksheets(1).Range("A2").Select
            Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    
    Application.ScreenUpdating = True
    
    
    End Function\
    note that RED lines are usually used...I'm not sure why people do that but I've always done it since I saw it the first time. this prevents excel from showing real-time interface updates of the data manipulation as the code continues to execute.

    I got an error here though (err: object required):

    Code:
    wkbk_open.Worksheets("Sheet1").Range ("A8:J") & _
            wkbk_open.Worksheets("Sheet1").Range("A65536").End(xlUp).Row.Copy
    your equivalent code to that error would be this code of yours:

    Code:
    With wkbk_open.Worksheets(Left(fname, 4))
                .Activate
                .Range("A8:J" & .Range("A65536").End(xlUp).Row).Copy
    just wanted to mention. as suggested too, your putting the data in new sheets as you go along, right? a while back I mentioned that you should put it all in memory and throw it into 1 sheet at the end. In my eyes, you can keep track of it better that way. why can't we do that? Just didn't think of it, perhaps?

    also notice that you do NOT have to activate sheets to get data from them into memory. Once more, I don't think you have to activate them to paste data FROM memory either. Just reference them with the "." I just did it in my example because I wasn't sure.

    doing good so far! almost there!

    by the way, I never gave you my own code to do this simply because you started on it yourself. so just trying to expand on your efforts...thanks.

  5. #20
    Tvanduzee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    39
    Thank you for the code snipets
    The reason I was putting it in 2 sheets is because when it comes to the workorder details, I need to separate them in access anyways. I could see no way to get the workorder info and the workorder details organized in Excel in a format that can be used in access.

    Right about now, I'm ready to throw in the towel as far as getting code to work doing things the way they need to be done.
    If you have a solution that will work for what I'm trying to achieve, I would be more than happy to do it/use it. I can send you several sheets if you like, so that you have more than 1 to go by.
    In order to import into access, I need the top part of the workorder to be put in a row, and then the details into rows as well, but the details need to be associated to the workorder number.

    Thanks so much
    Terry

  6. #21
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    Terry,

    I'd be happy to do it for you. but I have to re-iterate to you...

    ***attach an access file with examples of tables you're going to want***

    I know we've got through this for a while now, but showing me sample access tables with 1 record each in them and field names also in them will allow me to do it 1 time only.

    and yes, upload as many excel files as you want. but you've said many times so far that they are all identical in format. what I want the most though, are the output examples tables in access. I don't care how it looks, I just care how you want it to look.

    we'll get it done. no worries. good to hear from ya!

  7. #22
    Tvanduzee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    39
    Cool... thank you.
    I will go through the tables and put a record or 2 in each table. If during your work, you find there could be a better design, please feel free to let me know.

    Sorry for the delays, as a pc tech, I get called out all the time, plus I work full time.

    Terry

  8. #23
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    hey Terry. Was missing you...(just kidding).

    a PC tech?? how many passwords have you reset in the past week? ha ha....from my first job, that was always an ongoing joke. Apparently this problem is STILL a problem for corporate users.

    It still amazes me...

  9. #24
    Tvanduzee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    39
    LOL
    Yes, its a standing joke here too. But the problem is is that everyone expects you to remember a password you put on their system a year ago... although corporately, I do usually have their passwords....
    It's that and a call where the customer calls and says "I just got a call from microsoft and they went into my system and fixed some stuff"... those are the calls I like, because it is income for me....

  10. #25
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    hmmm...if MS is actually calling people I'm afraid they're being targeted for hackers. does MS actually call people. who would they be calling?

    software updates on corporate networks should be fully automated....all the ones I've been working from have been.

    but at any rate, upload the tables and what I requested and I'll get something back to ya.

  11. #26
    Tvanduzee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    39
    Sorry for the delay, I'm trying to get the final design down and work keeps me pretty busy too.
    No, MS is not calling people, these people call saying they are microsoft and that their computers are "really" broken, so they remote in and leave spyware/malware with fake scans. Then they tell the person, they can fix it, but they have to pay $59.95. And of course they don't actually fix anything. They also leave the remoting software on the system, giving them free access anytime they want. Corporately, yes, auto updates, but I do more than corporate, I also do public...

  12. #27
    help_me_with_access is offline help_me_with_excel
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    262
    hmmmm....how did you get into dealing with these kind of people, as a market pool? are these people some uninformed Christian folks that you get calls from? If that's the case, perhaps a *basic* 101 class in exploitation may be in order. I'm actually serious. Now that I think about it, if what you're saying it is true, that method of phone calling that people apparently use would be a better way to deploy a botnet than just sending SPAM email or attaching virus files to a website.

    Man....that's too bad. Where abouts do you live anyway? Heck, with my knowledge I could start a booming business down there regardless if it was black-hat or white-hat based! ha! (just kidding)

    but of course the first thing anyone in the USA should know about something like that would be...if MS *is* calling anyone on the phone (which I've never heard of such a case anyway), the person is going to be foreign and difficult to understand. If that's not the case, something is obviously wrong...

  13. #28
    Tvanduzee is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    39
    I work for a company that does corporate and public.
    I'm nearly done with the db design, but ran into a few snags. Not sure what Idid to mess it up. I will attach the db tomorrow when I get to work. I do have sample data in it, which can be deleted if necessary to make way for the real data.
    Not sure what I did on the frm_WO form to mess up the look up. And could not get the totals to work (previous item on other thread of ours.)

    Thanks

    Terry

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. vba books/tutorials
    By bigmac in forum Access
    Replies: 1
    Last Post: 03-10-2012, 02:05 PM
  2. Replies: 6
    Last Post: 08-04-2010, 01:16 PM
  3. Advanced books or training for Access
    By cowboy in forum Access
    Replies: 5
    Last Post: 02-25-2010, 11:22 AM
  4. SQL - Read any good books lately?
    By metaDM in forum Queries
    Replies: 2
    Last Post: 03-05-2009, 12:46 PM
  5. Ms Access Reference Books
    By dbman in forum Access
    Replies: 2
    Last Post: 01-27-2008, 06:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums