Copy a range from one worksheet to another

[JaxtheDog]

I am working in Access using VBA to processes spreadsheet lines. I copy failed lines to a second spreadsheet to re turn to the user. It is working using a range of cells and pastespecial to the second excel file. However, I can't get the column width to go from the original to the second workbook. Does anyone know a way to get the same cell width to the second one? I want it to match, and autofit will not work because some columns are deliberately shorter than the data.

Thanks--I appreciate any help you can give.

Jax

 

[SkipVought]

Please post your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JaxtheDog]

Here is generally the code I am using--this is one of many iterations. I made things visible so I could see what is happening. I open the first (existing) spreadsheet and then open a second one (blank). I have also tried creaing a new one. I pastespecial from the first to the second. Color and text paste, but not the column width.


Code:
Public Sub Command20_Click()
'routine imports requested doc records from submitted form.
Dim excel_app As Object
Dim excel_app2 As Object
Dim excel_wkbook As Object
Dim excel_wkbook2 As Object
Dim excel_sheet As Object
Dim excel_sheet2 As Object

Dim txtFilename As String
Dim headerRange As Range

txtFilename = OpenTextFile()

' open the existing file
Set excel_app = CreateObject("Excel.Application")
Set excel_wkbook = excel_app.Workbooks.Open(txtFilename)
Set excel_sheet = excel_app.Worksheets(1)
excel_app.Visible = True
excel_sheet.Visible = True


txtFilename = ""
txtFilename = OpenTextFile()

Set excel_app2 = CreateObject("Excel.Application")
Set excel_wkbook2 = excel_app2.Workbooks.Open(txtFilename)
Set excel_sheet2 = excel_app2.Worksheets(1)
excel_app2.Visible = True
excel_sheet2.Visible = True

excel_sheet.Range("A1:AB12").Copy

excel_sheet2.Activate

excel_sheet2.Range("A1").Select

'paste away
excel_wkbook2.Worksheets(1).Range("A1:AB12").PasteSpecial (xlPasteValues)
excel_wkbook2.Worksheets(1).Range("A1:AB12").PasteSpecial (xlPasteFormats)
excel_wkbook2.Worksheets(1).Range("A1:AB12").PasteSpecial (xlPasteColumnWidths)



I'd appreciate any help you can give. I need the column widths to match the first spreadsheet

 

[SkipVought]

What is this?

txtFilename = ""
txtFilename = OpenTextFile()

Do you have a function that returns a filename?

You migh try, excel_app.getopenfilename() Look it up in Excel VBA Help

Why are you setting TWO Excel Application Objects? Your copy will fail, I believe, with separate application objects.

BTW, I seem to recall answering a VERY SIMILAR issue (with two Excel Application Objects) recently. Strange.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JaxtheDog]

Skip,
I have tried this several ways, as I said. When I cut that code from the larger Proc to give you an example of what I was doing, I didn't remove the bit you referenced--and yes, it does open correctly when the function OpenTextFile fires.

Trying it various ways, I can always copy the cell contents from one workbook to another--I just can't get the column width. I have tried it in the same Excel instance, as you suggested, but can't get the formatting to copy.

I have created a template to get me around the problem, so I guess I'll just go with that. Thanks for looking at it.

Jax