+ Reply to Thread
Results 1 to 16 of 16

VBA code - importing text file to excel - variable filename!

  1. #1
    Registered User
    Join Date
    08-17-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Smile VBA code - importing text file to excel - variable filename!

    Hi,

    I am currently using the following line as part of code importing various files into an excel workbook. I now need to update the filename to import 'BSCEL P10 0910', where the numbers "10 0910" are variable.

    "Sheets("EL Data").QueryTables("BSCEL").Connection = "TEXT;" & ActiveWorkbook.Path & "\BSCEL.TXT"
    Sheets("EL Data").QueryTables("BSCEL").TextFileCommaDelimiter = True"

    I have so far tried changing the filename to the following. Any help would be much appreciated!!

    1 - "\BSCEL P" & Format(GeneralNumber,"##",Date,"yyyy") & ".TXT"
    2 - "\BSCEL P" & "*.TXT"
    3 - "\BSCEL P" & Format(1233456,"## ####") & ".TXT"
    4 - "\BSCEL P" & Format(GeneralNumber,"## ####") & ".TXT"

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code - importing text file to excel - variable filename!

    What is the significance of 10 0910 is it a period number and a year? Would prompting for the user to select the filename be an option?

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    08-17-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: VBA code - importing text file to excel - variable filename!

    Hi Dom,

    Yes, it is the period number and year. Prompting the use to manually select the filename isn't really an option due to the volumne of sheets and the number of csv files being imported each month.

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code - importing text file to excel - variable filename!

    Can you explain the logic that you would use to come up with 10 0910 on the date that the user would run the macro then?

    Dom

  5. #5
    Registered User
    Join Date
    08-17-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: VBA code - importing text file to excel - variable filename!

    Each month l run reports for each period in the last 2 years so l name then by the period number and year so l can identify them and ensure l am uploading the right information to the right sheets.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: VBA code - importing text file to excel - variable filename!

    Maybe something like:

    Please Login or Register  to view this content.


    I've added a "v" to what look like variable names. You then need a mechanism to get the right numbers into the variables.

    I think you were using Format incorrectly.

    Regards

  7. #7
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA code - importing text file to excel - variable filename!

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    08-17-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: VBA code - importing text file to excel - variable filename!

    I've tried both of these but it's not working - can't find the file.
    Is there someway to tell it to pick up the file by looking for the 'bscel' in the filename, whilst disregarding the rest of the filename?

    When l upload these the files for each period are kept in separate folders so l don't need it to know which period and year to look for.

    Thanks for your help!

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA code - importing text file to excel - variable filename!

    I assume you replaced 'E:\OF\' with the name of your folder ?
    Please Login or Register  to view this content.

  10. #10
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code - importing text file to excel - variable filename!

    This will pick up all files that fit that pattern in a directory:

    Please Login or Register  to view this content.

    Dom

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA code - importing text file to excel - variable filename!

    You can change the connection property of a querytable:

    You can't use joker-characters in the connectionstring of a querytable.
    But this will work:
    Please Login or Register  to view this content.

  12. #12
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code - importing text file to excel - variable filename!

    You might not be able to use them in the connection string but Dir will quite happily accept them and return the complete filename that you will then be able to use (I think).

    Dom

  13. #13
    Registered User
    Join Date
    08-17-2010
    Location
    Scotland
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: VBA code - importing text file to excel - variable filename!

    Tried
    Sub snb_002()
    c01 = "E:\OF\"
    c02 = Dir(c01 & "'BSCEL P" & Right(Format(Date, "yyyy"), 2) & "*.txt")
    Do Until c02 = ""
    With Sheets("EL Data").QueryTables("BSCEL")
    .Connection = "TEXT;" & c02
    .Refresh False
    Next
    Loop
    End Sub

    But error message says "Compile error : next without For"

    Entering another sub into the middle of the code re-sets the rest of the code. Is there any way to insert this as part of the current code? Full code below:

    Private Sub cmdRefresh_Click()
    ' connect to data in the same directory and refresh
    On Error GoTo errhandler
    Dim wks As Worksheet
    Dim qt As QueryTable
    Dim pt As PivotTable
    Dim strConn As String

    strConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password='';User ID=Admin;Data Source=" & _
    ActiveWorkbook.Path & "/Injury Factors.xls;Mode=Share Deny Write;Extended Properties=" & _
    "'HDR=YES;';Jet OLEDB:System database='';Jet OLEDB:Registry Path='';" & _
    "Jet OLEDB:Database Password='';Jet OLEDB:Engine Type=35;" & _
    "Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;" & _
    "Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password='';" & _
    "Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" & _
    "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
    "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"

    Sheets("Factors").QueryTables("Factors").Connection = strConn

    strConn = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password='';User ID=Admin;Data Source=" & _
    ActiveWorkbook.Path & "\Figtree Company Depot lookup table.xls;Mode=Share Deny Write;Extended Properties=" & _
    "'HDR=YES;';Jet OLEDB:System database='';Jet OLEDB:Registry Path='';" & _
    "Jet OLEDB:Database Password='';Jet OLEDB:Engine Type=35;" & _
    "Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;" & _
    "Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password='';" & _
    "Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" & _
    "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
    "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"

    Sheets("Depots").QueryTables("Depots").Connection = strConn

    Sheets("EL Data").QueryTables("BSCEL").Connection = "TEXT;" & ActiveWorkbook.Path & "\BSCEL.TXT"
    Sheets("EL Data").QueryTables("BSCEL").TextFileCommaDelimiter = True

    For Each wks In ActiveWorkbook.Worksheets
    For Each qt In wks.QueryTables
    Debug.Print qt.Name, qt.Connection
    qt.Refresh
    Next qt
    Next wks

    For Each pt In Sheets("Errors").PivotTables
    pt.RefreshTable
    Next pt

    MsgBox "All data has been refreshed", vbOKOnly, "Refresh data"

    Exit Sub

    errhandler:
    MsgBox "There was a problem refreshing the data - check that the data files are present in the same directory as this file.", vbOKOnly + vbExclamation, "Refresh failed"

    End Sub

  14. #14
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: VBA code - importing text file to excel - variable filename!

    You could have adapted the code according to the message: "Compile error : next without For"

    Please Login or Register  to view this content.

  15. #15
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: VBA code - importing text file to excel - variable filename!

    snb,

    I'm still not sure that's going to look for the query name in the correct format.

    Dom

  16. #16
    Registered User
    Join Date
    04-03-2014
    Location
    Braga, Portugal
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: VBA code - importing text file to excel - variable filename!

    Thanks a lot snb

    I've been looking for a solution for hours. Excel tales...

    Carlos

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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