+ Reply to Thread
Results 1 to 4 of 4

Data transfer problem with Macros

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Turku
    MS-Off Ver
    Excel 2010
    Posts
    6

    Data transfer problem with Macros

    I have a problem with my VBA macro that should transfer data automatically from C:\Temp\lingoni folder. Excel says that it can't find the files even tough they are at the folder and named correctly imo (?). The macro find's folders but not the files insde.

    In option explicit:

    Const keno = "\"
    Const alaviiva = "_"
    Const jatke = ".xlsx"


    And this is the Sub that should find the first file from C:\Temp\lingoni which is named as "2013_9_1" and the other 3 files are the same except that the last number is between 2-4. In the sub "vuosi" means year in Finnish language and "tehdas" means factory. I have named one cell as "vuosi" and other as "tehdas". Data_kansio is a name of one cell where I have writen "C:\Temp\lingoni" and "bu_kansio" is the name of backup folder inside lingoni folder.


    Macro:

    Function prosessilupa() As Boolean

    ' this function checks if the there are 2 folders and 4 files at the folder

    Dim is_ok As Boolean

    is_ok = True

    'are there 2 folders?

    If Dir(data_kansio, vbDirectory) = "" Then
    MsgBox ("There's no weekly data folder")
    is_ok = False
    End If
    If Dir(bu_kansio, vbDirectory) = "" Then
    MsgBox ("There's no backup folder")
    is_ok = False
    End If

    'are all 4 files inside the folders?

    If is_ok Then
    tehdas = 1
    Do
    datatdo = vuosi & alaviiva & viikko & alaviiva & tehdas & jatke 'datatdo on 1 tehtaan tiedot
    If Dir(data_kansio & keno & datatdo) = "" Then
    MsgBox ("Viikkodata puuttuu: tehdas " & tehdas)
    is_ok = False
    End If
    tehdas = tehdas + 1
    Loop Until tehdas > tehdaslkm Or Not is_ok
    End If

    prosessilupa = is_ok
    End Function

    Any ideas?

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Data transfer problem with Macros

    I can't see where the value of viikko is coming from?

    Try putting a breakpoint on the line after the one containing this term and hover over the datatdo term to see what it has evaluated to.
    Martin

  3. #3
    Registered User
    Join Date
    06-03-2013
    Location
    Turku
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Data transfer problem with Macros

    Also "viikko" refers to cell. Inside that cell is the input which is the week number. It didn't work, it still can't find the first file..

    I fould one mistake. It says that "viikko" cells value is 10 even tought the input inside the cell is 9. Any ideas why?
    Last edited by hemiso; 06-03-2013 at 01:36 PM.

  4. #4
    Registered User
    Join Date
    06-03-2013
    Location
    Turku
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Data transfer problem with Macros

    Now it works! I put viikko = Range("B13").Value to the macro. I still don't understand why it doesen't recognize cell named "viikko" input.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1