Importing Data from Excel File to Excel File in constantly changing di

[DSerr77]

I am trying to write a Macro that will look in the current folder for a file that will always be named and structured the same way and import the data into a sheet that will always be called the same thing. The number of rows on the sheet I am importing from will change. Any help is appreciated

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DSerr77]

Didn't know where to begin.

 

[EBGreen]

You could start by turning on the macro recorder and doing the steps you listed above then see what code is generated.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[DSerr77]

Here is what I have so far:

Sheets("Current Month").Select
Cells.Select
Selection.Clear
Range("A1").Select
Workbooks.Open Filename:= _
CurrentProject.Path + "\Current_Month_Detail.xls"
Sheets("Current_Month").Select
Cells.Select
Selection.Copy
Windows("1006 GL Revenue Detail.xls").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub

CurrentProject.Path does not seem to be working

 

[PHV]

CurrentProject.Path does not seem to be working
CurrentProject is an Access object, not an Excel object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DSerr77]

I am trying to use CurDir(filename), but I can not seem to get the correct syntax

 

[PHV]

Have you tried this ?
ThisWorkbook.Path + "\Current_Month_Detail.xls"

Or this ?
ActiveWorkbook.Path + "\Current_Month_Detail.xls"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

You have a workbook and you want to IMPORT data from another workbook with a name that changes periodically.

I do this often, using MS Query via Data/Get External Data...

First you set up the query to the CURRENT workbook.

Then macro record EDITING the query without changing it.

Then post back with your recorded code to customize it.

Skip,

_[red][/red]

 

[DSerr77]

Thanks for your help everyone. ThisWorkbook.path worked great! Thanks!