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

[dleckie]

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]

Sorry I could only upload one file at a time. Trying to send the read from workbook.

[dleckie]

Here is the read from Workbook.

[RonMcK]

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]

Doh... I should have thought of that... lol

[GTO]

Just reference getting the basic data you want...

[VBA]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
[/VBA]

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.

[VBA]Dim intDayOfYear As Integer
intDayOfYear = Date - #1/1/2008#
[/VBA]

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]

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]

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

[VBA]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[/VBA]

[dleckie]

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]

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:

[VBA]Private Sub CommandButton1_Click()
Retrieve_Config
End Sub
[/VBA]

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

[dleckie]

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]

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.

[GTO]

'************ 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 *******************

[vba]'************ 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 *******************
[/vba]

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]

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]

Dave,

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

[dleckie]

It works GREAT!!! Fantastic job you guys.

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