Importing From Closed Excel Workbook

[byrnen5]

Hi everyone,

I was wondering if someone could help out with some code. I'm doing a project at the moment and having difficulty starting it!!!!! (Newbie!) Basically i've got a blank workbook, with a button in it. When this button is clicked i want it to open up a dialog box so i can choose an excel file, and i want it then to copy the contents of that excel file and copy it to the workbook which contains the button.

Any help would be much appreciated as im very new to this and still at the early learning stages!

Thanks in advance

[Siddharth Rout]

Any help would be much appreciated as im very new to this and still at the early learning stages!

Hi byrnen5

Search the forums on the following

1) getopenfilename
2) Also see this tutorial.
http://www.vbforums.com/showthread.php?t=391665

Understand how to work with excel.

Once the above is done, experiment with code and if you get stuck then post the code that you have tried and we will definitely help you

[byrnen5]

hey thanks for the links.

ok so ive pretty much got it working, here's my code:

Sub CopyRanges()
Dim wb As Workbook
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.UsedRange.ClearContents

Next ws


fName = Application.GetOpenFilename("*,*.xls", , "Please select file to open")


If wb Is Nothing Then
Set wb = Workbooks.Open(fName)
End If
wb.ActiveSheet.Range("A1:Z1000").Copy
ThisWorkbook.ActiveSheet.Range("A5:Z200").PasteSpecial Paste:=xlValues

wb.Close

End Sub

Theres one thing that i want to change but not sure how to exactly. When it pastes in everything, i want cell values, formatting etc to be pasted also, so if a cell has a border its pasted in, how do i go about doing this?

[Siddharth Rout]

Change this

ThisWorkbook.ActiveSheet.Range("A5:Z200").PasteSpecial Paste:=xlValues

to

ThisWorkbook.ActiveSheet.Range("A5:Z200").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

[byrnen5]

cheers that worked perfectly, one other thing i forgot to mention, when pasting in, how can i get it to just paste in values, some cells use formulas to get there values, i dont wont to copy this in, just the value thats currently in the cell

thanks a million

[Siddharth Rout]

Need to issue different commands for different results.

Either do paste values in the relevant cells first and then do paste all or vice versa but you need to give two different commands.

[byrnen5]

i dont really know what you mean, sorry its just im not the best at this, i appreciate the help though

[Siddharth Rout]

Ok give me the cell names where you want to just paste values and cell where you want to paste all.

[byrnen5]

Columns h and j are the ones that get their values from formulas, every other value is directly inputted

so is there no way to paste everything as their values without specifying exactly which ones you want to do? like the last adjustment you made to my piece for pasting in formatting etc., is there nothing i can add to that to say paste in values only?

Thanks in advance

[Siddharth Rout]

every other value is directly inputted

When you say every other value, what range are we talking about?

Can you upload your workbook after zipping it?

[byrnen5]

I attached a sample file. The file i'll be importing will change slightly everytime (number of students and courses might change between courses or years) so therefore i cant give an exact range when importing the file (which is why i import a1:z200, just to be sure i get everything) from my file.

The column m values are gotten from a formula, but when my macro pastes this into the workbook im working on it gives me formula errors for column m's, which i why i want to just input the values. The formulas aren't needed when i paste them in, the original values suffice.

[Siddharth Rout]

Is this what you are trying? If not then do let me know...

  '~~> Paste with All formatting
ThisWorkbook.ActiveSheet.Range("A5:Z200").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

  '~~> Convert Column A to L into Values
ThisWorkbook.ActiveSheet.Columns("A:L").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

  '~~> Convert Column N to Z into Values. this will ensure
'~~> that Column M will retain formulas
ThisWorkbook.ActiveSheet.Columns("N:Z").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

[byrnen5]

I've got it working now, using:


ThisWorkbook.ActiveSheet.Range("A5:Z200").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ThisWorkbook.ActiveSheet.Columns("A5:Z200").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Worked perfectly

Thanks for your help

[Siddharth Rout]

Great, if your query is solved then do remember to mark your thread resolved