Solved: Import Macro help

[Adrianz]

mrexcel.com/tip025.shtml

Is there any way to change this so that it would import the new data to an existing worksheet within the document rather than create an entirely new sheet.

[lucas]

http://www.mrexcel.com/tip025.shtml

Do you mean that you will be updating this daily and you want each days import to be added to the same sheet...below the previous import?

[Adrianz]

Each day yes...But I want it to simply overwrite the previous import.

[rbrhodes]

Hi Adrianz,

From where? To where? Auto?

Perhaps a small example: before and after...


Cheers,

dr

[Adrianz]

Yes I want it to overwrite whatever exists already, that would be perfect. For example, if I am importing a document with 5 sheets, I would want the first sheet of the document to overwrite the second sheet of this document (due to the menu sheet being first), the second sheet of the imported document to overwrite the third sheet of this document, etc. Also if the macro doesn't find that a sheet exists, it will create a new one (this should only be an issue with the very first import) My code at the moment is the same as the sample code.

[lucas]

could you please post your workbook to keep us from having to build it from scratch to help you? also post your data file please. You can zip them up together..

[lucas]

Man, this thing is fluid and growing.....

[lucas]

The code you are using only imports one tab from the data file....

[Adrianz]

The code you are using only imports one tab from the data file....
How would I expand it to import every tab from the data file (and those overwrite the current tabs in the way I mentioned earlier)?

-Thanks-

[lucas]

Well....I'm still lost as to the objective....why don't you just copy the workbook if you want all fresh from the one your copying?

[Adrianz]

I'm looking to do this because eventually the data in some of these sheets will be graphed out and I need the graphs to update based on the new imported data.

[Adrianz]

Well....I'm still lost as to the objective....why don't you just copy the workbook if you want all fresh from the one your copying?

Yeah I haven't really done a good job explaining it all but here goes...

My goal is to have a workbook that a user can download. This workbook will have a set of graphs based upon data that the user downloaded. Every night we update this data. I want the user to be able to download the new data and for the graphs that either we provided for them or they created themselves to update based upon this new data.

[lucas]

You might be better off to copy a range from each sheet in the data workbook to a range in different sheets in the main workbook instead of importing sheets and deleting old ones....

[Adrianz]

Yeah that could definitely work. The original data is constantly expanding however...wouldn't it run into trouble when a new sheet was added to the original data?

[lucas]

how would your graphs know a new sheet has been added...they wouldn't and you would have to set that up each time right?

[Adrianz]

how would your graphs know a new sheet has been added...they wouldn't and you would have to set that up each time right?

Yeah I guess the graphs would have to be based off a selection of this data that I isolated separately into a new sheet. I'm more concerned with just getting the updating done for now and will focus on the actual formatting later.

I appreciate all the help so far!

[lucas]

Take a look at this Adrianz. You can include other sheets by adding another with statement...Would be glad to help but it would help to see your files.

[lucas]

I added one with statement to this one to show you how to add sheets to the scope. The ranges are very flexible...read the comments closely and if you have questions...

after you run the social results file once. Close it and save the changes..then open the social club file and make some changes to the range and close it and save it...then run the social results again and see the new updated data added to your sheets...

[Adrianz]

Sub GetFile()
Application.ScreenUpdating = False
Sheets("Menu").Select
PathName = Range("D3").Value
FileName = Range("D4").Value
TabName = Range("D5").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open FileName:=PathName
Sheets("Loans").Select
'Fix this value for each doc
ActiveSheet.Name = TabName
Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)
Windows(FileName).Activate
ActiveWorkbook.Close SaveChanges:=False
Windows(ControlFile).Activate
Sheets("Menu").Select
Range("D8").Select
ActiveCell.Value = "Last Update was"
Range("F8").Select
Selection.Formula = "=text(now(),""mmm dd yyyy"")"
Range("D9").Select
End Sub

How would I go about changing the bolded line so that it overwrites any sheets after the first sheet rather than creating new sheets after the first.

[Adrianz]

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating

'Make path selections below
' Set wb = Workbooks.Open("f:\Temp\Social Club.xls", True, True)
Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Social Club.xls")
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Final Results")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B7", "E36").Formula = wb.Worksheets("RESULTS").Range("B7", "E36").Formula
End With
With ThisWorkbook.Worksheets("Sheet1")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B7", "E36").Formula = wb.Worksheets("Sheet1").Range("B7", "E36").Formula
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub


I want to change the range values for the document being copied from so that the entire worksheet is copied. What should I put within the parenthesis for the range value?

-ty-

[mdmackillop]

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Dim Tgt As Workbook


Application.ScreenUpdating = False ' turn off the screen updating
Set Tgt = ActiveWorkbook
Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Social Club.xls")
'Copy sheet
wb.Sheets("RESULTS").Copy Before:=Tgt.Sheets(1)
'or Copy data
With Tgt.Worksheets("Final Results")
.Range("B7", "E36").Formula = wb.Worksheets("RESULTS").Range("B7", "E36").Formula
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

[Adrianz]

I also want to eliminate the range on the target paste location. I want the entire sheet to be copied over. Thanks so far.

[mdmackillop]

That is what this line does
wb.Sheets("RESULTS").Copy Before:=Tgt.Sheets(1)

[Aussiebear]

What is this line doing?

wb.Close False ' close the source workbook without saving any changes

:dunno

[mdmackillop]

What is this line doing?

wb.Close False ' close the source workbook without saving any changes
:dunno
Hi Ted,
It does this:
closes the source workbook without saving any changes

[Aussiebear]

Yes this wild colonial boy saw that through the mental haze, but in the context of the overall code, where we have just copied the contents of a sheet to another, and then we close without saving????
:dunno

[mdmackillop]

We close the Source, not the Target. One reason it's important to use variable names that are meaningfull and make code easier to follow.

[Aussiebear]

Thanks.

[rbrhodes]

Hi,

Here's a sheet with three Button/Module/Sub combinations.

One of them deletes ALL sheets except the "Menu" sheet and then copies in all sheets from the data file. Total replacement.

One of them deletes any sheet that it exists in the data file from the Main wb and replaces each one, leaving any others in the main file. Replaces only the sheets that are in the new file.

The last one looks at a list in Col B, leaves sheet "Menu" and any sheets that are in the list of sheets to keep and then deletes all other sheets. It then copies in all sheets from the data file.

Note for the last one: If a sheet in the list exists in the new data sheet the old sheetwill be kept but the new sheet with the same name will be copied in and named with the extension (2) as per Excel practice. It does NOT copy the data from the new sheet to the one chosen to be kept. Why would it? <G>

Cheers,

dr

[rbrhodes]

X post

http://www.ozgrid.com/forum/showthread.php?t=71619

Also related to:

http://www.vbaexpress.com/forum/showthread.php?t=13499

To quote lucas:

"Man, this thing is fluid and growing....."

Cheers,

dr

[lucas]

I agree dr, these two threads should probably be merged so that responders will have some understanding of what adrianz is trying to do.

Edit: threads merged

[Adrianz]

The main reason I didn't want to do a total replacement of all the sheets was because I had intended for the sheets to link to separate graphs also within the same workbook. If I did do a total replacement, the graphs would lose their source data correct? Either I need a way to only paste the new data into the existing sheets or for the pathing of the charts to change in the process.

[lucas]

Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating

'Make path selections below
' Set wb = Workbooks.Open("f:\Temp\Social Club.xls", True, True)
Set wb = Workbooks.Open(ActiveWorkbook.Path & "\Social Club.xls")
' the sheet in this workbook to copy to
With ThisWorkbook.Worksheets("Final Results")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B7", "E36").Formula = wb.Worksheets("RESULTS").Range("B7", "E36").Formula
End With
With ThisWorkbook.Worksheets("Sheet1")
' read data from the source workbook
'the range to copy to in this workbook-name of sheet to copy FROM-range in closed workbook to copy
.Range("B7", "E36").Formula = wb.Worksheets("Sheet1").Range("B7", "E36").Formula
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub


I want to change the range values for the document being copied from so that the entire worksheet is copied. What should I put within the parenthesis for the range value?

-ty-
To use this code in the way you desire you should change the ranges from B7 to A1 and change the E36 to a column and row to the right and down that you know will never be reached by your data...Z100 for instance if you don't think you wil ever use to the Z column and down to row 100.
.Range("A1", "Z100").Formula = wb.Worksheets("Sheet1").Range("A1", "Z100").Formula

[Adrianz]

This seems to be the best way for me to accomplish what I need. Any idea what the line would be to clear all the boxes containing %N/A (I get a ton of these as a result of the massive copy/paste macro)?

Thanks once again for all the help you have provided.

[lucas]

hmm...I'm not getting that problem here but you might try it like this:
.Range("A1", "Z100").Value = wb.Worksheets("RESULTS").Range("A1", "Z100").Value

can you post your workbooks?

[mdmackillop]

This seems to be the best way for me to accomplish what I need. Any idea what the line would be to clear all the boxes containing %N/A (I get a ton of these as a result of the massive copy/paste macro)?

Thanks once again for all the help you have provided.
Either
Cells.SpecialCells(xlCellTypeConstants, 16).ClearContents
or
Cells.SpecialCells(xlCellTypeFormulas, 16).ClearContents

[Adrianz]

Ahh, I was using ActiveRange as the range to copy from. Changed it to just a an expansive range value. Well this thread is officially solved! Ty everyone who helped out.

[lucas]

mark it solved using the thread tools at the top of the page Adrianz....glad you got it hashed out.