Import CSV File Using Cell As File Name & Active Workbook Path

Nov 3, 2009

I am trying to import some csv files so I can combine them, but am having probs with the filename and location.

Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With

With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................

View 5 Replies


ADVERTISEMENT

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

Mar 21, 2013

I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...

View 5 Replies View Related

Import Sheets Where File Path Is Date Based

Jan 8, 2008

I have a script that generates various worksheets based on the day and puts these in folders based on the year/month/day. So a report run today would be in the F:200817 folder.

At the end of each month and then again at the end of the year I need to pull all the reports into a single workbook. The macro I have now allows you to select the folder you want to import the sheets from, but it doesn't recurse subdirectories, so I can only import sheets from a specific day into a workbook, but not from a month/year.

View 9 Replies View Related

Cell Value Of Closed Workbook With Concatenated File Path In Cells

Sep 24, 2007

I have a report that is collated from several other workbooks. The source workbooks follow the same naming convention throughout the year and so I have managed to concatenate the file path to bring through the text of the filepath and cell reference however cannot work out how to get the value of the cell. For example, I have the file path in A1, file name in B1 and sheet and cell referernce in C1.

I am using {=(A1&B1&C1)} which gives me R:CONFIDENTIALREPORT FOLDER[REPORT 24.09.07.xls]Sheet1!$D$1. To make matters slightly more difficult, the source reports would be closed. Is there an easy way or am I going to have to do this in VBA.

View 4 Replies View Related

File Path From Cells In Workbook

Jul 23, 2014

here I have a few good and perfect codes, the issue is my systems changed so I need it to look in some different places for data.

basically I need to create a file path that includes the data from a cell. this cell is found be searching a table full of data.

see the code below

Code:

Sub saveandprint()
Dim MyPath As String
MyPath = MyPathstart & "Invoice" & "" & Format(Date, "dd-mm-yy")
MyPath1 = "Z:Invoices Temp GKL " & Range("B16").Value & Format(Now, "hhmm")

[Code]....

View 4 Replies View Related

Get Relative File Path Of Workbook

Feb 16, 2008

I work on multiple workbooks. They all calculate a lot of functions and I am forced to create multiple workbooks to do these calculations. Linking to other workbooks basically displays the results of workbook1 and works from there.

But...

I need to keep an overview of these files, so I store them in different folders all within the same 'master' folder. So let's say: master folder is FolderMaster, and this contains folderA, folderB, folderC. FolderA contains workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally folderC contains workbookC1 and workbookC2.

Suppose that this hierarchy might change. Moving eg workbook A2 to folderB. This would cause a #REF if it is linked in another workbook.

So I thought: organize your hierarchy in a 'master workbook' and store all paths to the individual files in a worksheet. If I link to a specific folder I do not directly link to the actual folder, but would use INDIRECT and grab the filepath from the masterworkbook. If the path to a workbook change I do not have to update all individual links, but only change the path in the masterworkbook. Right?

But 'how' is the big one here. How do you formulate your path to a file? It can not be the entire file path since that's variable... (sometimes 'hd:User1:....'; sometimes 'HD:User2'..."). So I think I need something as mentioned in this office help thing. But I have no clue. I do not want to use VBA, I hoped for another solution to this.

View 4 Replies View Related

Trim Full File Name & Path To File Path Only

Sep 27, 2006

I have a variable ("DestFile") that defines a path to a file (used in saving the file)...

I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...

How would I go about trimming the "DestFile" address to get a "DestFldr" address?...

An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?

View 4 Replies View Related

Create & Save Workbook To Same Path As Macro File

Aug 21, 2008

I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."

Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then
wks.Delete

End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................

View 5 Replies View Related

Macro To Append Multiple Xls File In Single Workbook From Specified Path

Jan 25, 2010

I need a macro to do the following

1.Get list of .xls files from specified folder.

2.Append the files in new workbook in same folder.

Ex:
D:/users/excel/Sample_sheet1.xls
D:/users/excel/Sample_sheet2.xls
D:/users/excel/Sample_sheet3.xls

I want to get the list of Sample_*.xls and
create Sample.xls master file which adds the above three .xls as sheets in it.

Actually my macro as below

View 10 Replies View Related

Import A .bas File Into A Specified Workbook

Jan 9, 2007

At work I have a folder full of .bas files containing useful UDF's.
At the moment I am the only person in my team that makes use of these files as importing a .bas file is beyond the rest of the team (i.e. then know it contains code so they get scared).

What I'd like to do is create a workbook where they can select one of the files (each file contains a single UDF), maybe see what it does (I put an explanation in the Comments section of the properties for each file) and then have it automatically imported into the workbook of their choice so they can make use of these functions.

View 9 Replies View Related

Automatically Open File In Specific File Path When Another Sheet Is Opened

Mar 20, 2014

I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls

So opening x.xls will automatically open y.xls

I've tried this in the workbook code area but it doesn't do anything.

[Code] .....

View 1 Replies View Related

Trying To Import File Into Current Workbook

Jul 21, 2008

Sub Macro1()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "COOPtbl1", "C:Documents and SettingskanegrMy DocumentsBook1.xls", True, ""

End Sub

Heres the code I keep getting a

"Run-time error '424':

Object required"

Message.

View 9 Replies View Related

Import Workbook Sheets In File

May 8, 2008

how to import an excel data file into another excel file? I have a file with a tab full of data that I need to import into another file and then I have to manipulate the data. I have figured out how to use an opendialoge box to select the file but after that i'm not sure how to get it into my file.

View 3 Replies View Related

Save File As Text Using Cells For Path & File Name

Dec 20, 2009

How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names

pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................

View 2 Replies View Related

User Input For File & Path For File Copy

Jan 26, 2008

Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?

Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub

View 4 Replies View Related

Import Sheet1 From A Selected File Into Another Workbook

Mar 1, 2010

I have a workbook I add report data to, in the form of imported sheets. I know how to do this with text files, but I'm lost on existing excel files. I want to add Sheet1 from one excel file to another workbook. I got this far, but I don't know how to tell the computer to import the data from Sheet1 in the old file into the new version of Sheet1 in the workbook. So I currently get a blank new sheet.

Sub OpenExcelFile()
Dim FileToOpen As String
Dim wks_exp_src As Worksheet
Dim i As Long
Dim strSourcePath As String
Dim strSourceName As String
Dim strSourceExt As String
Dim strNewShName As String
Dim strNameFinal As String
Dim FirstPos As Long
Dim bolShCreated As Boolean
ChDir ThisWorkbook.Path & "" ' "C:"

View 9 Replies View Related

Getting File-path From Selected Input File

Jan 29, 2014

getting full file path from selected input file?

I have a macro with text box and browse button.

User can click on browse button and it will allow them to select only text files.

Now for further processing I need to get the file path and file name in 2 different variables.

Like if user selected C:/Temp/Test.txt

Variable 1 should have C:/Temp
Varialble 2 should have Test.txt

View 2 Replies View Related

Copy File Name And Contents Of That File In A Specified Path?

Feb 5, 2014

I just found a code to copy file path and file contents. However it is copying folder path and folder contents.here is my requirement.I will specify a path, macro has to copy that file name in a particular cell, then it has to copy all its contents.
ex:

file name 1 has 3 sub files in it, file name 2 has 2 sub files in it--

column AColumn B
file name1file contents
file name1file contents
file name1file contents
file name2file contents
file name2file contents

And also i have extend this macro to write a index function. i will tell u about this. but first i need above thing I researched but I didn't find.

I need file name in a column then i need file contents in b column.

if a file has 10 file contents in a file then that file name should come 10 times in a column, and in b column all its contents.

View 14 Replies View Related

How To Have Excel File Open In Last Active Window / Workbook

Apr 15, 2013

I compared data between excel worksheets all the time, well recently my computer was upgraded from Windows XP to Windows 7 and since then I have not been able to figure out how to have an Excel file to open in the Excel window selected(or last active window) when I have more than one workbook open. Is there a way to do this?

View 5 Replies View Related

Saving Cell Contents To XML File Saved As Current Workbook File Name

Dec 12, 2012

I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.

I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.

Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.

Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy

[code]....

View 4 Replies View Related

Locate CSV File With Open File Msgbox And Import Columns From CSV To Worksheet

Oct 28, 2011

Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).

CSV files are something like that (I need only first 4 columns):

ABCDE1Name MSISDN Date Location MapLink 2M. Younus Safi "923***550577" "2011-10-07 20:36:18" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 3M. Younus Safi "923***550577" "2011-10-07 19:36:26" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 4M. Younus Safi "923***550577" "2011-10-07 16:39:58" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 5M. Younus Safi "923***550577" "2011-10-07 15:37:23" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com" 6M. Younus Safi "923***550577" "2011-10-07 14:37:05" "ACB Tower Badragah Chakwal. Badraga" "http://asdf.com"

and my Worksheet is like that (With a command Button):

I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on...

ABCDEF1NameMSISDN DatedBlankBlank Location2

3

4

5

6

7

View 4 Replies View Related

Import Text File Into File Where Rows Exceed Excel Row Limit

Jun 3, 2009

I have a workbook with many spreadsheet named Sheet1, Sheet2 and so on. Each sheet is filled completely upto 65536 rows. This data is being picked up from a CSV file. In this file there are sites with each site there is a assciated set of data. What happens is the data that extracts data does not differntiate between sites and when it reaches to the end of worksheet it splits the data into next sheet. So I am trying to create a macro to check each spreadsheet starting with the last sheet in the work book for example last sheet in the workbook is seven it should go to sheet6 and if there is a blank row after row 64000 it should cut all the rows and move them to sheet7. Then it should goto sheet5 and do the same and keep on doing it until it reaches sheet1.

View 9 Replies View Related

Save Hyperlink Path For File With Absolute Path And Non-Relative?

Mar 4, 2013

is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?

I notice that the hyperlink is ....pdf ry.pdf

if I change the position of the file excel there is a problem!

I would like to save es. d:invoichepdf ry.pdf

View 1 Replies View Related

Ensure Active Workbook When File Opened By Windows Scheduler

Jul 23, 2009

This code opens a UserForm in the Workbook_Open event when the file is opened. After 10 seconds, the message is supposed to go away if the user doesn't click anything, then macros that follow are supposed to run. It is intended to allow a user to stop the macros. This works when you open the file manually (macro security must be low), you'll see a msgbox pop up after the UserForm goes away.

However, when the workbook is opened as a Windows Scheduled Task (Windows/ Control Panel/ Scheduled Tasks), the UserForm does not come to the front of the screen unless a user actually clicks on the window. It just flashes at the bottom indefinitely.

View 6 Replies View Related

Copy Data From Open File To Current Active Workbook

May 2, 2008

I want to create a standard macro that will copy values from a Master workbook to the current, active workbook I have open. The problem I have run into is when I record a macro it also records the name of the active workbook I'm copying data into and I would like it so when I bring up a workbook I just run the macro and it copies the values in. I'll be doing 8 different macros that each copy different values from the Master spreadsheet

View 8 Replies View Related

Macro To Separate File Name And Path From Complete Path

Oct 3, 2012

I want to run a macro to separate File Name and Path from the given complete path

For Example

Code:

In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf

I need it separeted like

Code:

In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1

Is there any way to do this through a macro

View 2 Replies View Related

Split File Name From File Path

Feb 16, 2008

Each month I am sent a spreadsheet which contains a list of 100's of different file paths (including file name).

For example (Quater 1 is the document name)
R:ManagementJohn2008SalesSectionAQuater1

I would like to remove the file name from the file path (I will then convert the file paths to hyperlinks which will open the folder the document is stored in but not the document itself.

For example
R:ManagementJohn2008SalesSectionA

View 9 Replies View Related

Separate File Name From File Path

Jun 12, 2008

I am trying to separate a filename from a filepath in Excel VBA 2003.

I am using the Application. GetOpenFilename command to get the user to select the correct file.
However, this also seems to record the filepath.

I am trying to separate the filename out of this so that each file( name) activates a different sub procedure.
I am storing the result of the getopenfilename as a Variant.

At the moment I am having to specify the full path in order to get each sub to run. This is fine for 1 user - but not for others as the files are stored in different locations for each user.

View 7 Replies View Related

Open From Dialog Box To Insert File Path And Name Into Cell

Jun 21, 2009

Is there a way to use the open dialog box (like as if you where opening another workbook) and then with the selected item inster the path and file name into a cell.

View 2 Replies View Related

Change File Path In Formulas For Every Cell In A Sheet

Oct 9, 2009

I have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.

e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved