Padding in cells added to imported spreadsheet

[Don Kline]

I've got on spreadsheet that has the values I need for another spreadsheet.
The portion of the code I have been using is:

Workbooks.OpenXML Filename:=strSelectedFile, LoadOption:= _
xlXmlLoadImportToList
Set wbXMLSource = ActiveWorkbook
Cells.Select
Selection.Copy
wbXMLSource.Close (False)
Set wbXMLSource = Nothing
Application.DisplayAlerts = False
wbMain.Activate
wsXMLSource.Activate
Cells.PasteSpecial

The problem is that when the PasteSpecial takes place, instead of the values
being in columns, everything ends up in column 1. A picture of the end result
is below:


bridge_keyPolicy.ModalPremium 25000
What you can't see in this message is that the before the word "bridge" in
the above there are 6 boxes that magically appeared as spacers. Further the
25000 has a box between it and the end of "bridge_keyPolicy.ModalPremium".

What is supposed to be happening is that the "bridge_keyPolicy.ModalPremium"
should be in column 7 and the 25000 should be in column 8. The first six
columns should be blank.

Instead everything ends up being packed together in Column 1. All of the
rows are there - but everything is in Column 1.

How can I parse this in a macro?

 

[OssieMac]

Hi Don,

From the snippet of code you have provided I am completely confused by what
is the source data and what is the destination. However, I don't think you
can close the source that you are copying until after pasting to the
destination.

Try moving the following lines to after the Paste special.
wbXMLSource.Close (False)
Set wbXMLSource = Nothing

Also your paste special should have more parameters something like the
following.

Cells(1, 1).PasteSpecial Paste:=xlPasteValues

 

[Don Kline]

Moving the two lines as you suggested did solve the problem.

I'm good to go. Thanks for your help.