Help a newbie please - import data from external workbook

**stunner1901** · 11-07-2012, 06:54 PM

import variable worksheet from other workbook
Hi guys

I am a new poster and need help pls!!

I cannot post my files as unfortunately, i am trying to improve spreadsheets at work and they can't be sent externally.

To you guys it will be easy anyway i am sure.

Basically, i have created a reference file that uses various look ups, web queries and cross refs.
However, i need to import a variable worksheet in(employee list that changes weekly) so that a look up will work.

the problem i have is that they do not use a master (which would make it very easy) but instead create a new spreadsheet weekly that is named "A & A Tracker Week??". - Where ?? = current weeknumber

I have thought along the lines of using (Weeknum(today()) and trying to use "A & A Tracker Week & ("weeknum etc.,)" in a lookup but this is not working

My spreadsheet is "Emp Ref Checksheet" and i need 1 of the sheets from a&a copying over into this

I think i need a small vba program but need advice

Sorry it is a little bit vague but i am very rusty on the VBA front

Thanks in advance

**bentleybob** · 11-07-2012, 09:20 PM

I might suggest you put a cell in your reference file that you can enter the file name or even a part thereof (like the week number, but in text). Then you can use INDIRECT in your external references. For example, with the value (or text) "3" in cell A1 of the reference file, the following would pull in the value from cell G14 in sheet "Data" in file "A & A Tracker Week 3.xlsx". If the file isn't open, you of course have to add the path.

Please Login or Register  to view this content.

You can get fancy, if you like, by substituting your WEEKNUM formula for the reference to cell A1 in this if you're always going to refer to the current week's workbook. I personally would put the WEEKNUM formula in A1 in the referencing file just in case you want to refer to another file temporarily. That way, you change the reference once rather than in all the formuals referencing the other file.

**karagill** · 11-09-2012, 04:24 PM

I am new to this forum as well. I need a macro to import txt files into an excel spreadsheet. The problem I run into is that the file name changes everyday for example RMTmm_dd_yy.txt the date changes everyday when I create the file. I know I can write the code to pick the file but I want to skip this part if I can. Application.FileDialog(msoFileDialogFilePicker)
So I was hoping for two different macros:
The first one to remove the date from the txt file
The second one is to add the current date to the txt file in the same format as it was when it was created.
Any help with this would be GREATLTY appreciated as I am new to the Macro world

Thank you in advance!!
I have attached the code I have so far.

Sub IMPORT_FILE()
'
' IMPORT_FILE Macro
'

'
With Selection.QueryTable
.Connection = "TEXT;C:\Data\RMT10-03-12.txt"
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=-12
End Sub

**Alf** · 11-10-2012, 03:43 PM

@ karagill:

When you registred at the forum you "promised" to follow forum rules:

#2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

#3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing.

Alf