PDA

View Full Version : Solved: Copy cells with no spaces and paste with semi-colon



dleckie
09-29-2008, 08:45 AM
Hi All,

I am trying to create a macro in Workbook QTPData.xls that will:
1) Open an Excel Workbook called ConfigXXX, where XXX increments by version number
2) Goes to Tab ATS, copies the data in specific cells, excluding the spaces and pastes the data in QTPData Workbook ? Set AE Tree Tab. WITH a semi-colon ( ; ) in front of the data.

My two problems are
1) opening the ConfigXXX.xls workbook when the name has changed
2) Placing the semi-colon ( ; ) in front of the pasted data

I added the 2 workbooks I am using for reference.

Thank you in advanced.

dleckie
09-29-2008, 08:48 AM
Sorry I could only upload one file at a time. Trying to send the read from workbook.

dleckie
09-29-2008, 08:50 AM
Here is the read from Workbook.

RonMcK
09-29-2008, 10:01 AM
Sorry I could only upload one file at a time. Trying to send the read from workbook.
Dave,

ZIPing your several files is a work-around to the 'only 1 file' problem.

Cheers,

dleckie
09-29-2008, 10:32 AM
Doh... I should have thought of that... lol

GTO
09-29-2008, 12:13 PM
Just reference getting the basic data you want...

Sub Retrieve_Config()
'Location of data to copy
' rsa = ThisWorkbook.Path & "\Config.xls" '"c:\Config.xls"
' Application.Workbooks.Open (rsa)

Dim wbConfig As Workbook
'// Change path as desired//
Set wbConfig = Workbooks.Open(ThisWorkbook.Path & "\Config.xls")
'Copy ATS A3
' Windows("Config.xls").Activate
' Sheets("ATS").Select
' Range("A3").Select
' Selection.Copy
'Paste Set AE Tree C3
' Windows("QTPData.xls").Activate
' Sheets("Set AE Tree").Select
' Range("C3").PasteSpecial Paste:=xlPasteValues

'// Rather than copying/pasting special (vals only), you might want to consider//
'// this; as this resolves any extra spaces, ...
' ThisWorkbook.Worksheets("Set AE Tree").Range("C3").Value = _
' Trim(wbConfig.Worksheets("ATS").Range("A3").Value)

' ... as well as prefaces in the semi-colon if desired.//
ThisWorkbook.Worksheets("Set AE Tree").Range("C3").Value = _
";" & Trim(wbConfig.Worksheets("ATS").Range("A3").Value)

'...and so on til done


This leaves the issue of which file to open. Presuming that the 'Config' file will be a new file daily, and that the "xxx" could be a number, a simple solution would be to have this numbering correlate to the day of year.

Dim intDayOfYear As Integer
intDayOfYear = Date - #1/1/2008#


This of course would only select the correct file on the same day, so you might wwant something like an InputBox to enter the desired filename with...

dleckie
09-29-2008, 12:52 PM
Hi GTO,

I will look at the code tonight and respond tomorrow. I understand what you are saying and will try it out.

As for the numbering of the file, the number is the version of the config file that is sent to me. So Config 1.2.5.xls is what I am on now. The next version will be config 1.2.6.xls. I will be going to Config 1.3.0.xls or Config 2.0.xls in January depending on the build version we receive. I have no control in the naming the file. I could always rename but then I would have to remember which version I am on for which lab and iteration. Also saving the file for archive would be another issue.

Thanks

GTO
09-29-2008, 11:15 PM
Well than as you cannot control/predict the filenames sent, you might want to set the filename string thru an InputBox or the dialog box 'GetOpenFilename'.

Hope this helps,

Mark

Sub Retrieve_Config()
Dim strPath As String, _
wbConfig As Workbook
'// Change directory to wherever you're saving the files sent to you.//
ChDir (ThisWorkbook.Path)
'// Actually 'GetOpenFilename' should be able to change to a specified directory _
according to Help, but I couldn't figure this out. //
strPath = Application.GetOpenFilename(FileFilter:="Exel Workbooks (*.xls),*.xls", _
Title:="Open Config", _
MultiSelect:=False)
'// If Cancel is chosen, escape sub. //
If strPath = "False" Then
Exit Sub
'// Else, process as before... //
Else
Set wbConfig = Workbooks.Open(Filename:=strPath)
ThisWorkbook.Worksheets("Set AE Tree").Range("C3").Value = _
";" & Trim(wbConfig.Worksheets("ATS").Range("A3").Value)
ThisWorkbook.Worksheets("Set AE Tree").Range("D4").Value = _
";" & Trim(wbConfig.Worksheets("ATS").Range("A4").Value)
ThisWorkbook.Worksheets("Set AE Tree").Range("E5").Value = _
";" & Trim(wbConfig.Worksheets("ATS").Range("A5").Value)
'...statements...
'//...til all done, then close the opened file. //
wbConfig.Close SaveChanges:=False
End If
End Sub

dleckie
09-30-2008, 09:07 AM
Hi GTO,

This worked great... and a much better solution by opening the Open Config window and selecting the file I want. That way I can do multiple config worksheets for multiple tests and labs.

Ok it working now. How would I put a Macro Button on the 1st worksheet; named Global; to run this?

Thanks,

GTO
09-30-2008, 12:43 PM
There are a couple of different buttons you could install, one from the Forms toolbar, or an activex button from the vba tools menu. I don't know of any particular advantage to either for this case, so ... for installing a command button from the Forms toolbar...

Bring up the forms toolbar, select the command button, and click on your sheet where you want it. This will bring up the macro dialog box, where you will want to click on/select your sub procedure's name, and the <ok> button. To change the caption, simply right-click on the button, edit text...
======================================================

If for whatever reason you'd want an ActiveX command button instead, display the Visual Basic toolbar and click on the control toolbox. After selecting the command button and clicking on your sheet; you right-click and select Properties or View Code. You set the caption in the propertise window, and you need to enter the name of your procedure in the button's click procedure. Something like:

Private Sub CommandButton1_Click()
Retrieve_Config
End Sub


(You will note that this private sub was placed in the sheet's module by default.)

dleckie
10-01-2008, 06:48 AM
Thanks GTO,

There is one thing that I just realized. When I want to pull in NEW data from the ConfigXXX workbook to my QTP workbook, I need to delete the existing data. The range will probably change depending how much data neets to be tested.

I am guess that this needs to be placed at the top of the code, just below the DIM statement. I entered the following which is very symplistic but was wonder if there is a better way of coding it.


Sub RSAConfigData()

'Sub Retrieve_Config()
Dim strPath As String, _
wbConfig As Workbook

'************ here *******************
'Go to AE Tree tab and clear cell from range C3:XX
Sheets("Set AE Tree").Select
Range("C3:K12").Select
Selection.ClearContents

'Copy the contents from B3 to F6
Range("B3").Select
Selection.Copy
Range("F6").Select
ActiveSheet.Paste

Range("A1").Select
Sheets("Global").Select


'************ here *******************

'// Change directory to wherever you're saving the files sent to you.//

'ChDir (ThisWorkbook.Path)
'// Actually 'GetOpenFilename' should be able to change to a specified directory _
according To Help, but I couldn 't figure this out. //

'//Opens the New File window to retrieve the workbook you want.
strPath = Application.GetOpenFilename(FileFilter:="Exel Workbooks (*.xls),*.xls", _
Title:="Open Config", _
MultiSelect:=False)

'// If Cancel is chosen, escape sub. //
If strPath = "False" Then
Exit Sub
'// Else, process as before... //
Else
'//Copy and Paste each cell with semi-colon in from of it except the 1st one
'//Veterans Health Administration A3 -> C3
Set wbConfig = Workbooks.Open(Filename:=strPath)
ThisWorkbook.Worksheets("Set AE Tree").Range("C3").Value = _
Trim(wbConfig.Worksheets("ATS").Range("A3").Value)

'//VISN 16 - South Central VA Health Care Network A4 -> D4
ThisWorkbook.Worksheets("Set AE Tree").Range("D4").Value = _
";" & Trim(wbConfig.Worksheets("ATS").Range("A4").Value)

'blah blah blab


wbConfig.Close SaveChanges:=False
End If
End Sub

dleckie
10-01-2008, 06:51 AM
:think:
How did you copy/show the code you created above? I did a copy/paste, and it was definetally not the way you did it.
:doh:

GTO
10-02-2008, 04:37 AM
'************ here *******************
'Go to AE Tree tab and clear cell from range C3:XX
Sheets("Set AE Tree").Select
Range("C3:K12").Select
Selection.ClearContents

'Copy the contents from B3 to F6
Range("B3").Select
Selection.Copy
Range("F6").Select
ActiveSheet.Paste

Range("A1").Select
Sheets("Global").Select


'************ here *******************




'************ here *******************
'Go to AE Tree tab and clear cell from range C3:XX

'// Forget 'going to' the sheet. If you properly identify the range you //
'// are working with, you can simply perform the operation. //
'//OOPS!//
'ThisWorkbook.Worksheets("Set AE Tree").Range("C3:K12").Selection.ClearContents
ThisWorkbook.Worksheets("Set AE Tree").Range("C3:K12").ClearContents
'Copy the contents from B3 to F6
'// SAA//
ThisWorkbook.Worksheets("SET AE Tree").Range("B3") _
=ThisWorkbook.Worksheets("SET AE Tree").Range("F6")

Range("A1").Select
Sheets("Global").Select


'************ here *******************


As to making the code look sharp in the post, there's a little green/white icon that says 'VBA' at the top of the msg window. Click it and paste the code between the tags.

Mark

dleckie
10-02-2008, 07:06 AM
I tried the code you gave me and the 1st line works great. But the second like just delets that contents in cell B3.

What I wanted to do was COPY the contents from B3 to F6.

Thanks

RonMcK
10-02-2008, 10:08 AM
Dave,

ThisWorkbook.Worksheets("SET AE Tree").Range("B3") _
=ThisWorkbook.Worksheets("SET AE Tree").Range("F6") You're right, it's backwards. Change it to:
ThisWorkbook.Worksheets("SET AE Tree").Range("F6") _
= ThisWorkbook.Worksheets("SET AE Tree").Range("B3")

dleckie
10-02-2008, 11:10 AM
It works GREAT!!! Fantastic job you guys.

I am just amazed with this. Marking this one closed.